Can't INSERT INTO SELECT into a table with identity column

Question

In SQL server, I'm using a table variable and when done manipulating it I want to insert its values into a real table that has an identity column which is also the PK.

The table variable I'm making has two columns; the physical table has four, the first of which is the identity column, an integer IK. The data types for the columns I want to insert are the same as the target columns' data types.

 INSERT INTO [dbo].[Message] ([Name], [Type])
 SELECT DISTINCT [Code],[MessageType] 
 FROM @TempTableVariable

This fails with:

Cannot insert duplicate key row in object 'dbo.Message' with unique index 'IX_Message_Id'. The duplicate key value is (ApplicationSelection).

But when trying to insert just Values (...) it works ok.

How do I get it right?

You're probably missing an auto-increment declaration on `Message.Id` — bluevector, Jul 02 '12 at 15:15
`Message.Id` is identity and its identity increment is 1. That should be enough to do that, right? Otherwise I wouldn't be able to insert using the `Values(...)` syntax. — Eran Goldin, Jul 02 '12 at 15:19

score 0 · Accepted Answer · answered Jul 02 '12 at 15:16

0

It appears that the data "ApplicationSelection" is already in the database. YOu need to write the select to exclude records that are already in the database. YOu can do that with a where not exists clause or a left join. LOok up teh index to see what field is unique besides the identity. That will tell you what feild you need to check to see if teh record currently exists.

answered Jul 02 '12 at 15:16

HLGEM

94,695
15
113
186

`'ApplicationSelection'` isn't in the DB. And the table key is Id. – Eran Goldin Jul 02 '12 at 15:22
then you have applicationSelection in your query twice probably with two differnt values for the other field. It may not be the key but it appears to have a unique index. – HLGEM Jul 02 '12 at 17:19

Can't INSERT INTO SELECT into a table with identity column

1 Answers1