Bulk Insert and Identity Column

Hi,

   I have a table with column name "ID" which the primary key of the table 

with identity specification on. I m populating this table through bulk insert 

from a csv file which does no have the id column value. what will be the syntax 

for bulk insert when id column value is not present in the csv file.

Thanks

Are you using dataflow? If so, and since you do not have that column in csv source file, you just need to leave that column unmaped.

Take a look to Mike Davis's post.

No. I am doing it using SQL script. Not through SSIS. 

I want to knw if the bulk insert script script can be modified 

to handle this.

Yes, you can.

The syntax is show below:

USE AdventureWorks2008R2;
GO
DELETE HumanResources.myDepartment;
GO
BULK INSERT HumanResources.myDepartment
   FROM 'C:\myDepartment-n.csv'
   WITH (
      KEEPIDENTITY,
      FORMATFILE='C:\myDepartment-f-n-x.Xml'
   );
GO
SELECT * FROM HumanResources.myDepartment

Are you familiar with bcp? I've used bcp for some time without problems.

Let us know if you did it. And if so, mark the post as answer.

Good Luck

Thanks for the info. Will go through it.. One more thing i wanted

to know is if this whole process of formatting the file and then loading 

the data to the table be automated?

The problem is that your column file does not match the table column. Bulk Insert needs to know how to map columns even more if columns are in diferent order.

If you dont want a format file and want to turn this all process automatic, try to use a View. Yes, create a view without the identity column and BULK INSERT into that view.

Creating a view and inserting data into it works fine for me.

Thanks 

Glad to hear that. In fact turns things easir than maintain a format file.