Can't INSERT INTO SELECT into a table with identity column
- by Eran Goldin
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
END
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?
