insert into select from in a table with identity column

Question

Hi, I am using a insert into select from statement to insert the result set of a query into a table containing an identity column. I am consistently getting an error Cannot insert duplicate key row in object 'dbo.TABLE1' with unique index 'IX_TABLE1'.

Please help

Answer 1

The select statement you are using are retrieving rows that have duplicate values which when inserted into your table, fires up your constraint.

You may validate the column name through the unique index to which the data is being inserted. Once you get the column, try to check in your select statement which column this is mapped to and check for duplicate records. Although you may have an identity column, your table for insertion still contains constraints that links up with another column which prevents duplicate records.

Answer 2

For MSSQL use WHERE NOT EXISTS

INSERT INTO tbl1 (keycol, col1, col2)
SELECT keycol, col1, col2
FROM tbl2
WHERE NOT EXISTS
(SELECT 1
FROM tbl1
WHERE tbl1.keycol = tbl2.keycol);

For MySQL use INSERT IGNORE

INSERT IGNORE INTO table 
SELECT x, y, z 
FROM table2 
WHERE a=b

this will skip any duplicate key errors and just insert rows that do not collide with existing unique/primary keys.

Answer 3

All this error means is that you are trying to insert a value that already exists into the table. If you provide more info I can try and assist but you havent given a lot of info.