Jump to Navigation

298 - BULK INSERT with identity (auto-increment) column

1. Add an id column to the csv file and leave it blank:

------------------------------------------------------------

id,Name,Address
,name1,addr test 1
,name2,addr test 2

------------------------------------------------------------

2. Remove KEEPIDENTITY keyword from query:

BULK INSERT Employee  FROM 'path\tempFile.csv '
WITH (FIRSTROW = 2,FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n');

If you assign values to the id field in the csv, they'll be ignored unless you use the KEEPIDENTITY keyword,
then they'll be used instead of auto-increment.

 

3. Other way

Keep your table as it is and create this VIEW (select everything except the ID column)

CREATE VIEW [dbo].[VW_Employee]
AS
SELECT [Name], [Address]
FROM [dbo].[Employee];

BULK INSERT [dbo].[VW_Employee] FROM 'path\tempFile.csv '
WITH (FIRSTROW = 2,FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n');

 

 

Mssql:


Main menu 2

Story | by Dr. Radut