January 28, 2004 at 12:47 am
I created a clustered index with the options "Unique Values" and "Ignore duplicates" on a table, let's call it Table1. Table1 already has records in it.I chose the ignore duplicates so that I could insert a batch of records into with the hope that SQLSvr would insert records that didn't already exist in Table1 and ingore (not insert) any that already did exist in Table1. I've seen this work only with a simple test environment. However, when I try to use this in real life application, I executed the statement
INSERT INTO [123.12.13.34].database1.dbo.Table1
SELECT e.* from Table2 a join Table3 b on a.order_num = b.order_num
but it returned "Duplicate key ingored" and didn't insert any records! Why? In my tests on simple test tables, it would return this error, but still insert the unique values into Table1. In my simple test, my tables were in the same server/database. Note in the above example, Table1 is on a different server/database.
Anyone know how I can get this to work or why it didn't work? Thanks!
smv929
January 28, 2004 at 8:21 am
"I could insert a batch of records into with the hope that SQLSvr would insert records that didn't already exist in Table1 and ingore (not insert) any that already did exist in Table1."
I don't think it is good to enable 'Ignore duplicates' in unique key. In your case, you can achieve it by try
INSERT INTO [123.12.13.34].database1.dbo.Table1
SELECT e.*
from Table2 a
join Table3 b
on a.order_num = b.order_num
where a.order_num not in (select order_number from [123.12.13.34].database1.dbo.Table1 )
February 2, 2004 at 7:58 am
I agree that it's not a good idea to turn on the "ignore duplicate" option, however, I was just using this feature temporarily as a simple way to insert unique records (weeding out duplicate records). Basically, I'm just trying to understand the way this feature works. I know that you can write an INSERT the way you did above to prevent duplicates from being inserted. However, I thought it might be less overhead to use the "ignore duplicate" option. I have seen it work, but I'm puzzeled why in my case it didn't go ahead and insert the unique records. It just didn't insert anything at all.
smv929
February 5, 2004 at 7:30 pm
Take a look at your set xact abort setting. If its on all errors cause the transaction to fail.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply