Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sona
Yak Posting Veteran
68 Posts |
 Posted - 2001-12-20 : 01:12:37
|
| hello,I am using bulk insert command to insert from a file.If i get a primary key violation error then it automatically rollbacks the whole transaction.Is there any way that exactly at which record (or place in the file )there was a primary key error. |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-20 : 01:22:05
|
| bulk insert works in batch as my understanding goes. if you want to trap on which record the error is coming up, the most easiest way is using cursors or looping and adding one one records and commiting it on every insertion. there by whenever an error occours you can trap the error and also your already inserted records will not be rolledback.HTH-------------------------"Success is when Preparedness meets Opportunity" |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-12-20 : 04:03:03
|
| You can set the batch size to one row, so each row gets committed individually. Don't know how it will affect performance, though. See BULK INSERT overview in BOL.WITH (BATCHSIZE = 1) |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-20 : 08:11:17
|
I seriously doubt it. but am not sure, will try with some sample data and will get back on it. quote:
You can set the batch size to one row, so each row gets committed individually.
-------------------------"Success is when Preparedness meets Opportunity" |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-20 : 08:26:32
|
| this is what BOL says about Batchsize in Bulk Insert BATCHSIZE [= batch_size] Specifies the number of rows in a batch. Each batch is copied to the server as one transaction. SQL Server commits or rolls back, in the case of failure, the transaction for every batch. By default, all data in the specified data file is one batch. Arnold you got it right.-------------------------"Success is when Preparedness meets Opportunity" |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2001-12-20 : 08:36:58
|
| I know this exists, but always have to look it up. There's a TSQL command which you can use to ignore dup primary key rows. If the primary key row exists in the table already, the row you're attempting to insert will be ignored. BOL has it, I just can't remember the syntax right now, I'm only on my first cup of coffee.Mike "A program is a device used to convert data into error messages." |
|
|
|
|
|
|
|
|
primary key error