Supress PK violation error on insert?

[jsteph]

Hi all,
I just want to shove some data into a table and I know some of the source data is duplicated and I don't care.

I just want one of the dups to go into the destintation table and I want the query to continue on it's merry way without stopping as if the world ended.

I don't mind a message at the end saying "not all of your data was appended..." or whatever, but I don't want the world to stop because of a duplicate.

How can I do this without an aggregate/group-by source statement?

Thanks,
--Jim

 

[markros]

Use SSIS.

PluralSight Learning Library

 

[djj55]

Try a left join with the table you are inserting to.

INSERT INTO T2 (PK, a, b, c) 
SELECT PK, a, b, c 
FROM T1 
LEFT JOIN T2 
ON T1.PK = T2.PK 
WHERE T2.PK IS NULL

This will only insert the unique items.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[jsteph]

Is there any way to do this without using another tool? Such as a stored procedure directive?

I'm thinking along the lines of Set Xact_Abort but at the record-level. Whereas xact_abort off allows the procedure to continue if a statement fails, I'd like to continue the statement if a single record in, say, a bulk Insert statment fails.

No?

--Jim

 

[markros]

Not that I know of. Either use a suggestion from dij55 to exclude bad records or use another too. In T-SQL set based operations you can either update/insert all records or fail all. You can not do partial inserts.

PluralSight Learning Library