Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Inserting Data with Identity_Insert is ON with Insert into Table1 Select * from Table2

Tags:

sql

identity

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
like image 380
Born In Aban Avatar asked Oct 02 '12 10:10

Born In Aban


2 Answers

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 
like image 60
podiluska Avatar answered Oct 15 '22 08:10

podiluska


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.

like image 36
Mahmoud Gamal Avatar answered Oct 15 '22 08:10

Mahmoud Gamal