Data insertion with Identity_Insert is enabled using Insert into Table1 Select * from table2

Question

CREATE TABLE TestTable (id int IDENTITY (1,1), name nvarchar(100)); INSERT INTO TestTable(name) VALUES ('data1'); INSERT INTO TestTable(name) VALUES ('data2'); Declare @Identity as int set @identity=IDENT_CURRENT ('TestTable') SET IDENTITY_INSERT TestTable ON; 

How can I achieve this functionality?

 INSERT INTO TestTable Select * from ( Select 55 as a,'data55' as b Union Select 56 as a,'data55' as b )n 

When this is replaced, then .. Msg 8101, Level 16, State 1, Line 7 The explicit value for the identity column in the TestTable table can only be specified using a list of columns, and IDENTITY_INSERT is enabled.

 INSERT INTO TestTable (id, name) VALUES (55, 'data55'); INSERT INTO TestTable (id, name) VALUES (56, 'data55'); SET IDENTITY_INSERT TestTable OFF; DBCC CHECKIDENT (TestTable, reseed,@identity ) INSERT INTO TestTable(name) VALUES ('data3'); Select * from TestTable Drop table TestTable 

Answer 1

You need to say

 INSERT INTO TestTable (id, name) Select * from ( Select 55 as a,'data55' as b Union Select 56 as a,'data55' as b )n 

Answer 2

This should work correctly:

 SET IDENTITY_INSERT TestTable ON; INSERT INTO TestTable(id, name) Select * from ( Select 55 as a, 'data55' as b Union Select 56 as a, 'data55' as b )n; 

Just set SET IDENTITY_INSERT ON with the fields specified in the INSERT clause.