Bulk Inserting data from a CSV file

Dave

I have lost count how many times someone has required some data to be manipulated and their criteria is in a spreadsheet, e.g. Set all the accounts as closed. If the criteria is small you can include it in the where clause of your SQL statements. However if the spreadsheet is big, manually adding all the conditions to the where clause is tedious. One solution is to define a temporary table and manually populate it using numerous insert statements. An alternative is to bulk insert the criteria data from a CSV file which is what the below example shows.

CREATE TABLE #dr_test(
	AccountNo VARCHAR , 
	AccountName VARCHAR, 
	FirstName VARCHAR, 
	LastName VARCHAR, 
	email VARCHAR
)

BULK INSERT #dr_test
   FROM 'C:\accounts.csv'
   WITH 
      (
         FIELDTERMINATOR =',',
         ROWTERMINATOR ='\n',
         FIRSTROW = 1
      )

-- Additional statements here to join the temporary table to existing tables in the database.

DROP TABLE #dr_test

The disadvantage of this is that the file needs to sit on the DB server or on a network drive which brings access premissions into the mix. Unfortunately, if you are running SQL Server Management Studio on a client machine and connecting to a DB server from there you cannot pick up a file from your client machine.

Leave a Reply