>>>>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


I read the last line in your data file as Mike|Cole|Cretin and I wondered what I did to you. :-)

I will check this out. Thanks!