Do a bulk instert on a table with an auto increment id field

Question

Do a bulk instert on a table with an auto increment id field

Christopher Jack 1,616

Hi,

I have a table called Palletinfo .

I am trying to do a bulk insert on the table using a CSV and the following code

BULK INSERT Palletinfo
FROM 'C:\test\Palletlinfo.csv'
WITH (FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR='\n'
 );

How do I get it to skip the first ID field which is the auto increment one and insert the info into the second and third column on the database?

At the moment it is trying to upload the info into the first column.

Thanks

Accepted answer

3 additional answers

Your answer

Answer 1

Olaf Helper 47,516

See Use a Format File to Skip a Table Column (SQL Server) for how-to.

Answer 2

Yitzhak Khabinsky 26,586

The easiest way is to create a view (let's say vw_Palletinfo) without the

ID field which is the auto increment

After that just modify your statement as follows:

BULK INSERT vw_Palletinfo
 FROM 'C:\test\Palletlinfo.csv'
 WITH (FIRSTROW = 2,
     FIELDTERMINATOR = ',',
     ROWTERMINATOR='\n'
  );

Answer 3

Tom Phillips 17,771

The best way to use BCP/Bulk Insert is to insert into a temp or "stage" table, then merge the data from the stage table into the target table. So the stage table does not have an identity field.

Answer 4

Christopher Jack 1,616

Found the answer, you have to have the id as a blank column in the CSV file.

Is there any way to it in the code rather than on the csv?

Share via

Do a bulk instert on a table with an auto increment id field

3 additional answers

Your answer