declare @Command_BulkInsert VARCHAR(max)
declare @file VARCHAR(1000) = 'G:\RELEASE 5.11.26\MCR.UNET.CREPORT_ACCOUNTS.csv'
Declare @startRow varchar(10) = '2', @endRow varchar(10) = '1000000'
DECLARE @countryId INT

SELECT @countryId = countryId FROM customer.country WHERE countryCode = 'SN'


IF OBJECT_ID('tempdb..#AccountsForDelete') IS NOT NULL  DROP TABLE  #AccountsForDelete
CREATE TABLE #AccountsForDelete(accountId INT)

IF OBJECT_ID('tempdb..#AccountList') IS NOT NULL  DROP TABLE  #AccountList

CREATE TABLE #AccountList
(
	DAO VARCHAR(10),
    CustomerID VARCHAR(20),
    AccountCategory VARCHAR(20),
	Balance MONEY,
    Currency VARCHAR(6),
    AccountNumber VARCHAR(20),
    AccountStatus VARCHAR(50),
    AccountOpeningDate DATE,
    AccountLastTrans MONEY,
    AccountLastTransDate DATE,
    SystemDate DATE
)

SET @Command_BulkInsert = '
BULK INSERT #AccountList from ''' + @file + '''
with (CODEPAGE = ''ACP'', FIRSTROW = ' + @startRow + ', LASTROW = ' + @endRow + ', FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''' + CHAR(10)+ ''' , MAXERRORS=0)';

EXEC(@Command_BulkInsert)

UPDATE a SET a.statusId = 'inactive'
FROM customer.customer AS c
    JOIN customer.account AS a ON a.actorId = c.actorId
    LEFT JOIN #AccountList AS aa ON aa.CustomerID = c.customerNumber AND aa.AccountNumber = a.accountNumber
WHERE c.countryId = @countryId AND aa.AccountNumber IS NULL

INSERT INTO #AccountsForDelete
SELECT MIN(accountId)
FROM customer.customer AS c
    JOIN customer.account AS a ON a.actorId = c.actorId
WHERE c.countryId = @countryId GROUP BY a.actorId, a.accountNumber
HAVING COUNT(*) > 1

BEGIN TRY

	DELETE customer.account WHERE accountId IN (SELECT accountId FROM #AccountsForDelete)

END TRY
BEGIN CATCH
    DECLARE @errmsg nvarchar(2048) = error_message(), @severity tinyint = error_severity(), @state tinyint =error_state(),
        @errno int =  error_number(), @lineno int =  error_line()
    set @errmsg = 'Line ' + ltrim(str(@lineno)) + '. Errno ' +  ltrim(str(@errno)) + ': ' + @errmsg

    RAISERROR('%s', @severity, @state, @errmsg)

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
END CATCH