BULK INSERT into table with identity column?

[M Bourgon]

I have a file I'm trying to do some non-set-based processing with. In
order to make sure I keep the order of the results, I want to BULK
INSERT into a temp table with an identity column. The spec says that
you should be able to use either KEEPIDENTITY or KEEPNULLS, but I can't
get it to work. For once, I have full code - just add any file of your
choice that doesn't have commas/tabs. :)

Any suggestions, folks?

--create table ##Holding_Tank ( full_record varchar(500)) -- this
works
create table ##Holding_Tank (id int identity(1,1) primary key,
full_record varchar(500)) --that doesn't work

BULK INSERT ##Holding_Tank
FROM "d:\telnet_scri pts\psaxresult. txt"
WITH
(
TABLOCK,
KEEPIDENTITY,
KEEPNULLS,
MAXERRORS = 0
)
select * from ##Holding_tank

[Erland Sommarskog]

Re: BULK INSERT into table with identity column?

M Bourgon (bourgon@gmail. com) writes:

I have a file I'm trying to do some non-set-based processing with. In
order to make sure I keep the order of the results, I want to BULK
INSERT into a temp table with an identity column. The spec says that
you should be able to use either KEEPIDENTITY or KEEPNULLS, but I can't
get it to work. For once, I have full code - just add any file of your
choice that doesn't have commas/tabs. :)
>
Any suggestions, folks?

Even if you want the identity values to be generated for you, the column
still has to be in the file if you don't use a format file.

There are two way to go: add a 0 and a delimiter on each line in the
file, or use this format file:

8.0
1
1 SQLCHAR 0 0 "\r\n" 2 col ""

What I don't really remember is whether you actually can trust the
IDENTITY values to match the order the rows appear in the file. If you
want to play safe, you may want to write a program that reads the file
and adds a line number on each line in the file.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[M Bourgon]

Re: BULK INSERT into table with identity column?

Even if you want the identity values to be generated for you, the column
still has to be in the file if you don't use a format file.

Aha.

What I don't really remember is whether you actually can trust the
IDENTITY values to match the order the rows appear in the file. If you
want to play safe, you may want to write a program that reads the file
and adds a line number on each line in the file.

Okay, thanks again, Erland. I was afraid of that.