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

You may need to use the CURSOR to loop the query. For the first update to Table2, something is like this:

DECLARE @Col1 varchar(20);  
DECLARE @Col5 varchar(20);	-- Not used  
DECLARE @DATE_PAID date;  
DECLARE @AMOUNT_PD money;  
DECLARE c1 CURSOR FOR   
	SELECT t1.Col1, t1.Col5, temp.DATE_PAID, temp.AMOUNT_PD   
	FROM Table1 AS t1   
	INNER JOIN #tempTable1 AS temp ON temp.PERMIT_INS = t1.Col2;  
OPEN c1  
FETCH NEXT FROM c1 INTO @Col1, @Col5, @DATE_PAID, @AMOUNT_PD;  
  
WHILE @@FETCH_STATUS = 0    
BEGIN   
	UPDATE Table2  
	SET col3 = @AMOUNT_PD, Col4 = @DATE_PAID  
	WHERE Col2 = @Col1;  
  
	FETCH NEXT FROM c1 INTO @Col1, @Col5, @DATE_PAID, @AMOUNT_PD;  
END  
CLOSE c1;    
DEALLOCATE c1;  

You need another loop similar to the first one to update Table4. But there is no relationship between Table 3 and #tempTable1. Not sure how to update Table4.

Answer 3

Hi, Thanks for the response. Would it be possible to modify this portion of the code :

 SELECT t1.Col1, t1.Col5, temp.DATE_PAID, temp.AMOUNT_PD   
 FROM Table1 AS t1   
 INNER JOIN #tempTable1 AS temp ON temp.PERMIT_INS = t1.Col2;  

and

BEGIN
UPDATE Table2
SET col3 = @AMOUNT_PD, Col4 = @apl _PAID
WHERE Col2 = @Col1;

 FETCH NEXT FROM c1 INTO @Col1, @Col5, @DATE_PAID, @AMOUNT_PD;  

END

to something like

 SELECT t1.Col1, t1.Col5, temp.DATE_PAID, temp.AMOUNT_PD, t3col, t4.col  
 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  

and

BEGIN
UPDATE Table2
SET col3 = @AMOUNT_PD, Col4 = @apl _PAID
WHERE Col2 = @Col1;

 UPDATE Table3  
 SET col3 = @AMOUNT_PD, Col4 = @DATE_PAID  
 WHERE Col2 = @Col1;  

 UPDATE Table4  
 SET col3 = @AMOUNT_PD, Col4 = @DATE_PAID  
 WHERE Col2 = @Col1;  
  
 FETCH NEXT FROM c1 INTO @Col1, @Col5, @DATE_PAID, @AMOUNT_PD;  

END

Assuming I change the Update Table3 and Update Table 4 variables to match the appropriate columns and values or do these Updates need to be handled separately.

Answer 4

Hi @Paul Bill ,

After checking your original post and making some tests in my own environment, it is only needed to update the values of table2 and table4 according to the change of Filename.csv and not very necessary to use cursor.

You could refer below example and check whether it is helpful to you.

UPDATE T2   
SET T2.col3=T.AMOUNT_PD , T2.COL4=T.DATE_PAID  
FROM TABLE2 T2  
INNER JOIN table1 T1 ON T1.col1=T2.col2  
INNER JOIN #tempTable1 T ON T.PERMIT_INS=T1.col2  
  
UPDATE T4  
SET T4.col3=T.NAME  
FROM TABLE4 T4  
INNER JOIN TABLE1 T1 ON T1.col1=T4.COL1  
INNER JOIN #tempTable1 T  ON T.PERMIT_INS=T1.col2  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

I'll check both of these out and let you know how the went.