bulk insert with identity column

Today, I was looking for geting a solution to insert a lot of line into SQL server with high performance. I got a way is using BULK INSERT ... to insert huge record into table. It is really high performance ( insert a 50MB text file into DB cost about 10s). How to use the command, you can get the detail from here(http://msdn.microsoft.com/en-us/library/ms188365.aspx). I don't want to introduce the usage of this command. I'd like to introduce the way if there is an identity column in the table, and you don't want to insert any value in this column. How to do it?

There are serveral solutions can be used for this, 

1. BULK INSERT record into a temporary table, then select into the records to actual table. I don't think this is a good solution for high perfornamce insert record. Because we need write one record twice.

2. Use format file to skip some columns. We also use BULK INSERT command, but we need define a format file. You can use command bcp to do this work. E.G. "bcp d2d.dbo.tbl_log format nul -n -f abc.xml -T".

The table schema like this:

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

id              int  identity(1,1),

create_time     datetime,

type            nvarchar(16),

job_id          nvarchar(16),

message         nvarchar(1024)

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

So, the format file will like this:

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

8.0

5

1       SQLINT        0       4       ""                        1     id              ""

2       SQLDATETIME   1       8       ""                        2     create_time     ""

3       SQLNCHAR      2       32      ""                        3     type            SQL_Latin1_General_CP1_CI_AS

4       SQLNCHAR      2       32      ""                        4     job_id          SQL_Latin1_General_CP1_CI_AS

5       SQLNCHAR      2       2048    ""                        5     message         SQL_Latin1_General_CP1_CI_AS

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

According MSDN description, we can set some attributes to zero to indicate which column we want to skip. Just like:

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

2       SQLDATETIME   0       0       ""                        0     create_time     ""

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

But it didn't work when I following this. I don't know why it didn't work, may be my source file with invalid format or my use it with a wrong way. Anyway, I got an another way to resolve this problem.

3. The easiest and workable way is put a whitespace in the identity column. SQL server will ignore it. My source file like this:

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

  - 2009/11/12 23:11:12 - Information -   - Server time zone information: id="GMT-07:00",useDaylight="false" 

  - 2010/05/06 07:01:19 - Information - Job00000001 - Incremental backup

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

The SQL statement is:

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

begin transaction T1

bulk insert d2d.dbo.tbl_log from 'G:\\testSQLBulk.txt'

with (

    FIELDTERMINATOR =' - ',

    ROWTERMINATOR ='\n',

)

commit transaction T1

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

Finally, I found the BULK INSERT command isn't a transcation operation. So, we should create custom transcation when using it.