>>>The second question is still valid though. When I add the ID field, it fails because it attempts to insert my first column value from my file into that field, instead of autoinc.
>>
>>You would move autoinc to the far right at least one + than the count of incoming columns.
>>Cetin
>
>OK, I didn't realize it was that easy. I wonder when somebody will complain that it's at the end instead of at the beginning... :-)

It is not:) Sorry that answer was given at 4 AM just before going to bed. If it were the case SQL server would be faous for having identity columns at the end. I noticed my mistake but it was too late PC was shutting down.

There are several ways:
-In place of identity column in the file there is data (dummy, ie:0). W/o keepidentity it wouldn't be imported but auto created.

-Use a non-XML or XML format file (I think this is a better option). Use bcp utility to create a format file for your table. ie:

Use myDb
go
Create table myTable (
 personId int identity primary key,
 firstName varchar(20),
 lastName varchar(20),
 reserved datetime default getdate(),
 submittedBy varchar(20)
)

Create base format file at command prompt (say we are working at c:\temp\sqlBulkLoad):

bcp MyDb..myTable format nul -S .\sqlexpress -T -c  -f mytable.fmt -t "|" -r "|\n"

This creates mytable.fmt file that looks like this:

8.0
5
1       SQLCHAR       0       12      "|"                       0     personId        ""
2       SQLCHAR       0       20      "|"                       2     firstName       SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       20      "|"                       3     lastName        SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       24      "|"                       0     reserved        ""
5       SQLCHAR       0       20      "|\r\n"                   5     submittedBy     SQL_Latin1_General_CP1_CI_AS

Here is our test text file (c:\temp\SQLBulkLoad\myFile.txt):

first|last|by|
cetin|basoz|cetin|
john|doe|cetin|
mike|cole|cetin|

Now, in text file we only have 3 fields ( for this we simply remove the columns to be skipped and fixup the column orders in the leftmost position - this is one way, there are other ways)

8.0
3
1       SQLCHAR       0       20      "|"                       2     firstName       SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       20      "|"                       3     lastName        SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       20      "|\r\n"                   5     submittedBy     SQL_Latin1_General_CP1_CI_AS

We are ready to bulk insert.

BULK INSERT
	MyTable
FROM
	'c:\temp\SQLBulkLoad\MyFile.txt'
WITH
(
	FormatFile = 'c:\temp\SQLBulkLoad\MyTable.fmt', firstrow=2
)

Cetin