| Author |
Topic |
|
sbushway
Starting Member
18 Posts |
 Posted - 2003-12-09 : 10:09:53
|
| Does bulk insert usually have a problem inserting into a table with an autonum ID field?I have an ASP.NET application that I'm working with. On one of the pages, I'm reading a file, parsing it and putting the pipe character between the fields, and writing it to another file. Then I'm doing a bulk insert from that delimited file. In the table I want to insert to, I have an ID field that's an autonum. When I run the program, if I keep the ID field in my table, I get an error about truncating the data I'm trying to insert (because I don't have any data in my source file that would go into the ID field). But if I delete the ID field from the table, the bulk insert works fine.This is bad since I *must* have an ID field in the table.Is there a way to do a bulk insert and still keep the ID field??Thanks in advance,Suzanne |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-12-09 : 10:26:31
|
| SET IDENTITY_INSERT <tablename> ONTo retain the id's...is the table empty?Make sure to set it back off...Brett8-) |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-12-09 : 10:35:56
|
| Easiest way is to create a view on the table which excludes the identity and bcp into that.You could also use a format file and exclude the column.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sbushway
Starting Member
18 Posts |
Posted - 2003-12-09 : 10:37:20
|
| Let me make sure I understand what you're saying:Run the 'SET IDENTITY_INSERT <xxx> ON' command before my bulk insert and 'SET IDENTITY_INSERT <xxx> OFF' after my bulk insert?Also, the table gets truncated before I run the bulk insert.Thanks for your help,Suzanne |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-12-09 : 11:30:47
|
| That won't help.If you had the identity values in the file you would use keepidentity for bulk insert, -E for bcp.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sbushway
Starting Member
18 Posts |
Posted - 2003-12-09 : 11:41:43
|
| nr,I'd prefer to not use format files. But using a view would be an option, too?Thanks for your help!Suzanne |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-12-09 : 12:05:47
|
| or bulk insert into a staging table (w/o the identity), then INSERT INTO your actual tables (with the identity column, but allowing it to default) from there. This can be useful if you need to perform validation or checking or summarizing or anything like that. but it does add another step to the process.- Jeff |
|
|
|
sbushway
Starting Member
18 Posts |
Posted - 2003-12-09 : 14:41:14
|
| Thanks, Jeff!I didn't even think about using a temp table. That worked great.~Suzanne |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-09 : 15:58:58
|
quote:
Originally posted by sbushway
nr,I'd prefer to not use format files. But using a view would be an option, too?Thanks for your help!Suzanne
He didn't mention a format file. He mentioned passing switches to the commands. This can be done in both BULK INSERT and bcp.exe. Have a look at BOL for more details about it.Tara |
|
|
|
|
Bulk Insert with Autonum Problem