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 |
|
bertherngreen
Starting Member
2 Posts |
 Posted - 2007-12-21 : 04:13:31
|
| I'm using SQLXMLBulkload to bulkload some tables.Sometimes it happens that a duplicate record is inserted.This is not right because of PK violation, I wrote an "INSTEAD OF INSERT" trigger for the table which updates the record. This trigger works fine when Fired by an "INSERT INTO [Table] Values ()" but seems to be bypassed by SQLXMLBulkload because it still gives a PK violation error.The proces that creates the XML-input file, can't be altered to remove the duplicate records.Is it true that SQLXMLBulkload bypasses "INSTEAD OF INSERT" triggers? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-21 : 04:18:08
|
Why don't you set the IgnoreDuplicateKeys property accordingly?http://msdn2.microsoft.com/en-us/library/aa225730(SQL.80).aspx
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-21 : 04:18:46
|
quote:
IgnoreDuplicateKeysSpecifies what to do if an attempt is made to insert duplicate values in a key column. If this property is set to TRUE and an attempt is made to insert a record with a duplicate value in a key column, SQL Server does not insert that record. But it does insert the subsequent record; thus, the bulk load operation does not fail. If this property is set to FALSE, Bulk Load fails when an attempt is made to insert a duplicate value in a key column.When the IgnoreDuplicateKeys property is set to TRUE, a COMMIT statement is issued for every record inserted in the table. This slows down the performance. The property can be set to TRUE only when the Transaction property is set to FALSE, because the transactional behavior is implemented using files.The default value is FALSE.
It seems you don't have to use the TRIGGER at all.It's amazing what amount of information the help files contains.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
bertherngreen
Starting Member
2 Posts |
Posted - 2007-12-21 : 04:29:49
|
| To use this I have to disable the "Transaction = True" option and I would like to avoid this! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-21 : 04:37:34
|
It's your system, not mine.Do as you like and prefer.Maybe you should use a staging table for import?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
SQLXMLBulkload, INSTEAD OF INSERT triggers