Inserting Data with Identity_Insert is ON with Insert into Table1 Select * from Table2
17,212
Solution 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
Solution 2
This should work properly:
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 listed in the INSERT
clause.
Author by
Born In Aban
Updated on July 18, 2022Comments
-
Born In Aban almost 2 years
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 i can 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 An explicit value for the identity column in table 'TestTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.
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
-
podiluska over 11 yearsMy requirement is a Ferrari and a mansion in the country. That doesn't affect the rules of the language.