January 19, 2011 at 5:15 pm
I'm trying to insert about 9,000 records from a table in one database into an identical table with about 200 records in a different database. I ran this intersect query to find any records with duplicate keys in either table. All the columns that make up the Primary key are in the select clause.
select Company_Code,Main_Account,Sub_Account,Transaction_Type,Transaction_Number
from dbo.Check_AP
intersect select Company_Code,Main_Account,Sub_Account,Transaction_Type,Transaction_Number
from oldDB.dbo.Check_AP[/font]
3 records were returned which I deleted from the receiving table. Then I ran this insert stmt.
insert dbo.Check_AP
select * from oldDB.dbo.check_ap
Upon completion I had about 500 fewer records in the table than the total of the two tables before the insert. There was a message that "Duplicate key was ignored"
I'm stumped!
January 19, 2011 at 8:07 pm
"Duplicate key was ignored" means that a matching key already existed in your target table, so that row was not inserted. It's a setting on an index, so that instead of failing the entire insert, the insert proceeds without creating duplicates. You must have had 500 records with keys that already were in the table.
It can be useful when you know you have duplicates in your source, and want unique values in the target.
January 19, 2011 at 8:55 pm
Thanks for the reply. I thought that the intersect query I ran would find all of the duplicate keys. It found 3 using all of the columns that make up the PK.
January 20, 2011 at 12:05 pm
Look at your unique indexes to see which one has "Ignore Duplicate Key" selected.
I suspect you overlooked something.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply