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?