Bulk Insert and Update MSSQL

Bulk insert is a good feature that inserts data into a table from any file at great speed. But this option is only applicable for insertion there is no option for updation. If we have a primary key in our table then many times we face the problem “Cannot insert duplicate key”. I faced the same problem while using bulk insert so made a manual way to handle updates during bulk insert.

I will explain you the whole process which i followed with a sample

Create a CSV file:
Consider a CSV file containing email, firstname, lastname and DateOfBirth of clients as below and save the file as Test.csv in C:\

naveen@gmail.com,Naveen,Prabhu,08/04/1983
aatish@yahoo.com,Aatish,Sethi,10/08/1984
gautham@hotmail.com,Gautham,Nayak,10/08/1984
manas@rediff.com,Manas,Patnaik,10/08/1984
raghu@India.com,Raghu,Kini,10/08/1984

Create a table TestCSV:
For the above CSV file we need to create a table with the same fields, here email will be a unique key so the table can be designed as,

Create table TestCSV
	(
		Email Nvarchar(100),
		FirstName varchar(100),
		LastName varchar(100),
		DOB datetime,
		Constraint PK_TestCSV Primary Key(Email)
	);

Create a stored procedure for Insertion and Updation:

Create PROCEDURE InsertClientInfo( 
@CSVPath NVARCHAR(200))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--Create a Temporary table to input the data from CSV file    
Create table #LookUpTable
	(
		Email Nvarchar(100),
		FirstName varchar(100),
		LastName varchar(100),
		DOB datetime,
	);

	--Declare a Variable to hold the command for Bulk insert    
Declare @UpdateQuery NVArchar(200)
	Set @UpdateQuery = 'bulk insert #LookUpTable
	from ''' + @CSVPath + ''' with(
	fieldterminator = '','',
	rowterminator =''\n'')';

	--Execute the Bulk Insert command
	Exec SP_ExecuteSQL @UpdateQuery

	--Check if Email from is already in the database and delete those from TestCSV
	Delete from TestCSV where Email in (Select distinct Email from #LookUpTable);

	--Insert the Updated or New entries in TestCSV table
	Insert into TestCSV select Distinct * from #LookUpTable;

	--Drop the temporary table
	Drop table #LookUpTable;
END

Run the Stored Procedure:

InsertClientInfo 'C:\Test.csv'

This will insert the new records and update the old ones from the file Test.CSV into TestCSV table

Allow Duplicates
If your table does not have a primary key and can accept duplicates then the stored procedure will be simple and will look like

Create PROCEDURE InsertClientInfo( 
@CSVPath NVARCHAR(200))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Set @UpdateQuery = 'bulk insert TestCSV
	from ''' + @CSVPath + ''' with(
	fieldterminator = '','',
	rowterminator =''\n'')';

	--Execute the Bulk Insert command
	Exec SP_ExecuteSQL @UpdateQuery
END

Hope you are clear with the article, If you have any other good solution for this issue, Kindly help.

Happy Learning