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
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With