Insert help

Question

Insert help

Oracle765

SSCrazy

Points: 2862
More actions
March 6, 2013 at 5:51 pm

#269379

I am trying to insert records into the database as follows
insert into lookuptable
select * from lookuptable1
which produces
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'uqLookuptable1cols1'. Cannot insert duplicate key in object 'dbo.lookuptable'.
The statement has been terminated.
Is there a way to handle this error so I can carry on inserting the rest of the records into the table

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

Hi,

You can re-create your index with IGNORE_DUP_KEY = ON and then it will allow inserting duplicate keys, otherwise you'll have to select distinct key values for the insertion.

Igor Micev,My blog: www.igormicev.com

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Do you know what would really help, besides direct access to your system? The DDL for the table(s), some sample data for the tables, the expected results of the query you are working on based on the sample data, and all of this in a readily consumable (meaning cut/paste/run in SSMS) format.

It is really hard to provide good answers based on just some code that apparently doesn't really work.

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

alan_lynch (3/6/2013)
I am trying to insert records into the database as follows
insert into lookuptable
select * from lookuptable1
which produces
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'uqLookuptable1cols1'. Cannot insert duplicate key in object 'dbo.lookuptable'.
The statement has been terminated.
Is there a way to handle this error so I can carry on inserting the rest of the records into the table

Maybe a bit of splitting hairs but this is NOT an error. You are trying to insert data that is in violation of the rules you have established.

You should just change your insert statement slightly.

insert into lookuptable

select * from lookuptable1

where WhatEverColumnMustBeUnique not in (select WhatEverColumnMustBeUnique from lookuptable)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 4

Sean Lange (3/7/2013)
alan_lynch (3/6/2013)
I am trying to insert records into the database as follows
insert into lookuptable
select * from lookuptable1
which produces
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'uqLookuptable1cols1'. Cannot insert duplicate key in object 'dbo.lookuptable'.
The statement has been terminated.
Is there a way to handle this error so I can carry on inserting the rest of the records into the table
Maybe a bit of splitting hairs but this is NOT an error. You are trying to insert data that is in violation of the rules you have established.
You should just change your insert statement slightly.
insert into lookuptable
select * from lookuptable1
where WhatEverColumnMustBeUnique not in (select WhatEverColumnMustBeUnique from lookuptable)

Wait, wait, wait! I think it's a mistake to assume that the OP just wants to insert rows where the key values don't exist in the table while ignoring the duplicate key rows. Let's make sure first.

So, @alan_lynch, what do you want to happen when you try to insert a new row into this table and the key value already exists there? Ignore that new row or overwrite the values in the existing row with the values from the new row?

Using a MERGE instead of an INSERT gives you the flexibility to do either without failing when the new rows include key values that already exist in the target table.

This will insert new rows where the key values don't exist in the target and ignore new rows where the key values do exist in the target table:

MERGE INTO lookuptable t

USING lookuptable1 s

ON t.key_col = s.key_col

WHEN NOT MATCHED

THEN INSERT(key_col, col1, col2, col3)

VALUES(s.key_col, s.col1, s.col2, s.col3)

;

This will overwrite existing data with new data when the key values already exist and insert rows when the key values do exist:

MERGE INTO lookuptable t

USING lookuptable1 s

ON t.key_col = s.key_col

WHEN MATCHED

THEN UPDATE

SET t.col1 = s.col1, t.col2 = s.col2, t.col3 = s.col3

WHEN NOT MATCHED

THEN INSERT(key_col, col1, col2, col3)

VALUES(s.key_col, s.col1, s.col2, s.col3)

;

Either way, no duplicate key errors AND the target table data is correct.

EDIT: It just occurred to me that all of the code suggestions posted here assume that there is a 0-or-1 to 1 relationship between lookuptable and lookuptable1. If that relationship could be 0-or-1 to many (e.g., there could be multiple rows in lookuptable1 with the same values in the columns that comprise the primary key of lookuptable), none of these suggestions will really work. @alan_lynch, if you still need help, can you clarify your requirement and provide the DDL and sample data @Lynn Pettis requested?

Jason Wolfkill