DECLARE @MerchantId INT = 1; -- M_TEST
DECLARE @Count INT = 100;


DECLARE @MerchantCode varchar(max) = (SELECT TOP 1 MERCHANT_ID FROM prod.dbo.Merchants WHERE Id = @MerchantId);

CREATE TABLE #Sap (OperationId CHAR(16), [MsId] VARCHAR(MAX), [Date] DATETIME );

INSERT INTO #Sap
SELECT TOP (@Count) O2.OperationId, O2.MsID, O2.ProcessDate AS [Date] FROM PROD.dbo.Operation2  O2
WHERE [MerchantId] = @MerchantId AND MsID IS NOT NULL
ORDER BY RecordDate desc


INSERT INTO [SapDBServer].[SAP_IntercompanyTransactions_Prod].[dbo].[Transactions] ([OperationId], [TransactionId], [MsId], [MerchantReimbursmentDate], [MerchantId], PaymentDate, SAP_Conciliated)
SELECT OperationId,
'999999' AS TransactionId,
[MsId],
 DATEADD(HOUR, 1, [Date]) AS 'Date',
 @MerchantCode AS MerchantId,
  DATEADD(HOUR, 2, [Date]) AS 'Date',
 'Y'
  FROM #Sap


INSERT INTO [SapDBServer].[SAP_IntercompanyTransactions_Prod].[dbo].[MerchantBankSettlement]
([MerchantId], SettlementCancelled, SapDocNum, SettlementBankId, PaymentCompany, SapSettlementId, SapPaymentWizardId, [SettlementISOCurrCode], [SettlementCreationDate], [SettlementPaymentDate], [SettlementAmount], [HasMultipleMsId])

SELECT DISTINCT @MerchantCode, 'N', ABS(CHECKSUM(NEWID())), [MsId], N'', 0, 0, '', GETDATE(), GETDATE(), 0, 0
  FROM #Sap


INSERT INTO [SapDBServer].[SAP_IntercompanyTransactions_Prod].[dbo].[MerchantBankSettlementDetail] ([OperationId], [MsId], [MerchantBankSettlementId])

SELECT OperationId,
[MsId],
(SELECT TOP 1 Id FROM [SapDBServer].[SAP_IntercompanyTransactions_Prod].[dbo].[MerchantBankSettlement] WHERE [MsId] COLLATE SQL_Latin1_General_CP850_CI_AS = [SettlementBankId])
  FROM #Sap




DROP TABLE #Sap



 -- clear data
 --DELETE from [SapDBServer].[SAP_IntercompanyTransactions_Prod].[dbo].[Transactions] where TransactionId = '999999' AND ShopperID IS NULL
 --DELETE from [SapDBServer].[SAP_IntercompanyTransactions_Prod].[dbo].[MerchantBankSettlementDetail] where MsId in (select SettlementBankId from [SapDBServer].[SAP_IntercompanyTransactions_Prod].[dbo].[MerchantBankSettlement] where LEN(SettlementBankId) > 10)
 --DELETE from  [SapDBServer].[SAP_IntercompanyTransactions_Prod].[dbo].[MerchantBankSettlement] where LEN(SettlementBankId) > 10