jana
asked on
How to solve ' identity column' SQL Message in MsSql 2000 when copying table to another
We're doing a copy table with the following script:
DELETE DATA db2.dbo.temp
go
INSERT INTO db2.dbo.temp
select * from db1.dbo.ppemp
We get the following message:
Server: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'datebase.dbo.table' can only be specified when
a column list is used and IDENTITY_INSERT is ON.
It's a lot of tables we need to do 'set' this column, so:
- How can we display or know which column have ' identity column'?
- How de we 'set' these columns OFF and then ON?
DELETE DATA db2.dbo.temp
go
INSERT INTO db2.dbo.temp
select * from db1.dbo.ppemp
We get the following message:
Server: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'datebase.dbo.table' can only be specified when
a column list is used and IDENTITY_INSERT is ON.
It's a lot of tables we need to do 'set' this column, so:
- How can we display or know which column have ' identity column'?
- How de we 'set' these columns OFF and then ON?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i'm still getting the "dentity column" error message:
Server: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'datebase.dbo.table' can only be specified when
a column list is used and IDENTITY_INSERT is ON.
Server: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'datebase.dbo.table' can only be specified when
a column list is used and IDENTITY_INSERT is ON.
As the error clearly says, specify column names like this
USE db2
GO
DELETE FROM dbo.temp
go
SET IDENTITY_INSERT dbo.temp ON;
INSERT INTO dbo.temp (col1, col2, col3)
select col1, col2, col3 from db1.dbo.ppemp
SET IDENTITY_INSERT dbo.temp OFF;
USE db2
GO
DELETE FROM dbo.temp
go
SET IDENTITY_INSERT dbo.temp ON;
INSERT INTO dbo.temp (col1, col2, col3)
select col1, col2, col3 from db1.dbo.ppemp
SET IDENTITY_INSERT dbo.temp OFF;
ASKER
the the columns is a must to include. Thera many tables and each tables has lots of columns, there is no other way?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanx
ASKER
SET IDENTITY_INSERT tablename ON
SET IDENTITY_INSERT tablename OFF
But it won't set it off, why?