I have two table exactly same.But when I try to copy one to another (empty), I get an error message.

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'TransactionLine2' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Because I have a primary key in the table.If wediscard the id,copy issue is succesfuly done.But,I need to copy them.

After that,I tried this codes.

SET IDENTITY_INSERT [X].[Transactions] ON
GO

INSERT INTO [X].[Transactions] select * from [x].[TransactionLine]

GO

SET IDENTITY_INSERT [X].[Transactions] OFF

GO

Hi Ceakn,

Use explicit column list instead of *, then your statements would work.

CREATE TABLE testTBL(ID INT IDENTITY,name VARCHAR(20))
CREATE TABLE testTBL2(ID INT IDENTITY,name VARCHAR(20))

INSERT INTO TestTBL(name) VALUES('A name')
GO
 
--insert successfully.
SET IDENTITY_INSERT testTBL2 ON
GO
INSERT INTO testTBL2(ID,name) SELECT ID,name FROM testTBL
GO
SET IDENTITY_INSERT testTBL2 OFF

SELECT * FROM testTBL2

DROP TABLE testTBL,testTBL2

If you have any question, feel free to let me