Creating a script to Import a .csv file to a temp table to create updates to other tables within the same database to be ran daily.

Question

Can anyone give me an example or syntax on how to use the temptable once it is imported to loop through the records in the temptable and update other tables data based on the current row's data. I have my import portion of the script done, I'm just not sure of the proper syntax for SQL Server or if something like this can be done. Any help is appreciated.

CREATE TABLE #tempTable1
(
PERMIT_INS VARCHAR(50),
AMOUNT_PD DECIMAL(10,2),
DATE_PAID DATETIME,
NAME VARCHAR(150)
)

BULK INSERT #tempTable1
FROM 'C:\Filepath\Filename.csv'
WITH
(
FirstRow = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);

Now from here I would like to do the following :

loop through the #tempTable1 and

Select the Idfield(column1) and column5 from table1 where #tempTable1.PERMIT_INS = table1.column2

Update table2 set table2.column3 = #tempTable1.AMOUNT_PD and table2.column4 = #tempTable1.DATE_PAID where table2.column2 = table1.column1

Select the Idfield(column1) and column3 from table3 where table1.column1 = table3.column2

Update table4 set table4.column3 = #tempTable1.NAME Where table1.column1 = table3.column 1

End Loop

Drop #tempTable1

Answer 1

If the query with the multiple JOINs works for you, that is okay. Be careful with the LEFT JOIN which might result NULL. And also you need the same numbers of columns (7 for your example) between SELECT statement and FETCH INTO. Something like this:

DECLARE @Table1Col1 varchar(20);
DECLARE @Table1Col5 varchar(20); -- Not used
DECLARE @Table3Col1 varchar(20);
DECLARE @Table4Col1 varchar(20);
DECLARE @DATE_PAID date;
DECLARE @AMOUNT_PD money;
DECLARE @NAME varchar(20);
DECLARE c1 CURSOR FOR 
 SELECT t1.Col1, t1.Col5, temp.DATE_PAID, temp.AMOUNT_PD, temp.NAME, t3.Col1, t4.Col1 
 FROM Table1 AS t1 
 INNER JOIN #tempTable1 AS temp ON temp.PERMIT_INS = t1.Col2
 LEFT OUTER JOIN Table3 AS t3 ON t1.Col1 = t3.Col2
 LEFT OUTER JOIN TABLE4 AS t4 ON t3.Col1 = t4.Col2
OPEN c1
FETCH NEXT FROM c1 INTO @Table1Col1, @Table1Col5, @DATE_PAID, @AMOUNT_PD, @NAME, @Table3Col1, @Table4Col1;

WHILE @@FETCH_STATUS = 0  
BEGIN 
 -- Update Table3 ...

 -- Update Table4 ...

 FETCH NEXT FROM c1 INTO @Table1Col1, @Table1Col5, @DATE_PAID, @AMOUNT_PD, @NAME, @Table3Col1, @Table4Col1;
END
CLOSE c1;  
DEALLOCATE c1;

Answer 2

Ok, after working on your answers I discovered I actually had 2 insert statements and one update statement and modified your answer to the script below. Will this work? If so, I have another dilemma, when I'm declaring c2 below, it is possible that more than 1 fee may be returned with the same invoiceid. How would I determine if the @Amount_Pd is greater than the FeeAmt for the first record and what would the syntax look like to handle that situation until the @Amount_Pd value is 0?

CREATE TABLE #tempTable1
(
PERMIT_INS VARCHAR(50),
AMOUNT_PD DECIMAL(10,2),
DATE_PAID DATETIME,
NAME VARCHAR(150)
)

BULK INSERT #tempTable1
FROM 'C:\filePath\filename.csv'
WITH
(
FirstRow = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);

--t1 = Invoices
--t2 = Invoice Payments
--t3 = FeePayments
--t4 = Fees
DECLARE @InvoiceId uniqueidentifier;
DECLARE @BillToPartyId uniqueidentifier; -- Not used
DECLARE @InvoiceNumber varchar(50);
DECLARE @RecordId uniqueidentifier;
DECLARE @InvoiceBalance DECIMAL(10,2);
--DECLARE @Table3Col1 varchar(20);
--DECLARE @Table4Col1 varchar(20);
DECLARE @apl _PAID date;
DECLARE @AMOUNT_PD money;
DECLARE @DeezNutz varchar(20);
DECLARE c1 CURSOR FOR
SELECT INV.InvoiceId, INV.BillToPartyId, INV.InvoiceNumber, INV.RecordId, temp.DATE_PAID, temp.AMOUNT_PD, temp.NAME
FROM Invoices AS INV
INNER JOIN #tempTable1 AS temp ON temp.PERMIT_INS = INV.InvoiceNumber
--LEFT OUTER JOIN InvoicePayments AS INVP ON INV.InvoiceId = INVP.InvoiceId --t3
--LEFT OUTER JOIN TABLE4 AS t4 ON t3.Col1 = t4.Col2 --t4
OPEN c1
FETCH NEXT FROM c1 INTO @InvoiceId, @BillToPartyId, @InvoiceNumber, @RecordId, @apl _PAID, @AMOUNT_PD, @DeezNutz ;

WHILE @@Fetch _STATUS = 0
BEGIN
Declare @NewId uniqueidentifier;
Set @NewId = NewId();
INSERT INTO InvoicePayments(PaymentId, InvoiceId, PaymentUserId, PaymentDate, PaymentAmount, PaymentType)
VALUES(@NewId, @InvoiceId, @BillToPartyId, @InvoiceNumber, @apl _PAID, @AMOUNT_PD, 'Credit Card');

Declare @FeeId uniqueidentifier;  
DECLARE @FeeAmt money;  
DECLARE @PaymentAmt money;  
Declare @PaymentDate date;  

DECLARE c2 CURSOR FOR  
Select FeeId, FeeAmt, PaymentAmt, PaymentDate FROM FEES Where InvoiceId = @InvoiceId  

OPEN c2  
FETCH NEXT FROM c2 INTO @FeeId, @FeeAmt, @PaymentAmt, @PaymentDate;  
  
WHILE @@FETCH_STATUS = 0    
	BEGIN  
	-- Update Table3 ...  
		Insert Into FeePayments (FeeId, PaymentDate, PaymentAmt, PaymentType, PaymentUserId, InvoicePaymentId)  
		Values (@FeeId, @Date_Paid, @Amount_PD, 'Credit Card', @BillToPartyId, @NewId);  

      
	-- Update Table4 ...  
		Update Fees Set PaymentDate = @DATE_PAID, PaymentAmt = @AMOUNT_PD, PaymentUserId = @BillToPartyId Where FeeId = @FeeId;  

		FETCH NEXT FROM c2 INTO @FeeId, @FeeAmt, @PaymentAmt, @PaymentDate;  
	END  
	CLOSE c2;    
	DEALLOCATE c2;  

END
CLOSE c1;
DEALLOCATE c1;

--drop table #tempTable1

Answer 3

I take that back, it's only processing the first record in the .csv file, the .csv file looks like this:

This invoice has two separate fees one for $1 and one for $2 that need their fee payments made individually then the invoice Balance updated.

This is my current code. Any help is appreciated.

CREATE TABLE #tempTable1
(
PERMIT_INS VARCHAR(50),
AMOUNT_PD DECIMAL(10,2),
DATE_PAID DATETIME,
NAME VARCHAR(150)
)
BULK INSERT #tempTable1
FROM 'D:\Frederick County Fee Import Test\FrederickCounty.csv'
WITH
(
FirstRow = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);

--c1 = Invoices Joined with the temp table.

DECLARE @InvoiceId uniqueidentifier;
DECLARE @BillToPartyId uniqueidentifier;
DECLARE @InvoiceNumber varchar(50);
DECLARE @RecordId uniqueidentifier;
DECLARE @InvoiceBalance DECIMAL(10,2);

DECLARE @apl _PAID date;
DECLARE @AMOUNT_PD money;
DECLARE @DeezNutz varchar(20);
DECLARE c1 CURSOR FOR
SELECT INV.InvoiceId, INV.BillToPartyId, INV.InvoiceNumber, INV.RecordId, temp.DATE_PAID, temp.AMOUNT_PD, temp.NAME
FROM Invoices AS INV
INNER JOIN #tempTable1 AS temp ON temp.PERMIT_INS = INV.InvoiceNumber

OPEN c1
FETCH NEXT FROM c1 INTO @InvoiceId, @BillToPartyId, @InvoiceNumber, @RecordId, @apl _PAID, @AMOUNT_PD, @DeezNutz ;

WHILE @@Fetch _STATUS = 0
BEGIN
-- Invoice Payments ..
Declare @NewId uniqueidentifier;
Set @NewId = NewId();
INSERT INTO InvoicePayments(PaymentId, InvoiceId, PaymentUserId, PaymentDate, PaymentAmount, PaymentType)
VALUES(@NewId, @InvoiceId, @BillToPartyId, @apl _PAID, @AMOUNT_PD, 'Credit Card');

Declare @FeeId uniqueidentifier;
DECLARE @FeeAmt money;
DECLARE @PaymentAmt money;
Declare @PaymentDate date;

--c1 = Fees on the invoice.
DECLARE c2 CURSOR FOR
Select FeeId, FeeAmt, PaymentAmt, PaymentDate FROM FEES Where InvoiceId = @InvoiceId Order By FeeDate

OPEN c2
FETCH NEXT FROM c2 INTO @FeeId, @FeeAmt, @PaymentAmt, @PaymentDate;

WHILE @@Fetch _STATUS = 0
BEGIN
Set @PaymentDate = GetDate();

Declare @RemainingAmt DECIMAL(10,2)
Declare @InvoiceAmt DECIMAL(10,2)
Set @InvoiceAmt = @AMOUNT_PD
Set @RemainingAmt = @InvoiceAmt - @FeeAmt
-- Insert FeePayments ...
Insert Into FeePayments (FeeId, PaymentDate, PaymentAmt, PaymentType, PaymentUserId, InvoicePaymentId)
Values (@FeeId, @PaymentDate, @Amount_PD, 'Credit Card', @BillToPartyId, @NewId);
-- Update Fees ...
Update Fees Set PaymentDate = @apl _PAID, PaymentAmt = @FeeAmt, PaymentUserId = @BillToPartyId, DateUpdated = @PaymentDate Where FeeId = @FeeId;

IF(@RemainingAmt=0)
BEGIN
Update Invoices Set InvoiceBalance = @AMOUNT_PD, DateUpdated = @PaymentDate Where InvoiceId = @InvoiceId;
END
FETCH NEXT FROM c2 INTO @FeeId, @FeeAmt, @PaymentAmt, @PaymentDate;
END
CLOSE c2;
DEALLOCATE c2;

FETCH NEXT FROM c1 INTO @InvoiceId, @BillToPartyId, @InvoiceNumber, @RecordId, @apl _PAID, @AMOUNT_PD, @DeezNutz ;
END
CLOSE c1;
DEALLOCATE c1;

drop table #tempTable1

The resulting query posts this:

(2 rows affected)

(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)