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?
rayluvsAsked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
This should do:

USE db2
GO
DELETE FROM dbo.temp
go
SET IDENTITY_INSERT dbo.temp ON;
INSERT INTO dbo.temp
select * from db1.dbo.ppemp
SET IDENTITY_INSERT dbo.temp OFF;
0
 
rayluvsAuthor Commented:
Ok, I found it:

               SET IDENTITY_INSERT tablename ON
               SET IDENTITY_INSERT tablename OFF

But it won't set it off, why?
0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
>> But it won't set it off, why?

You need to turn it off manually once the INSERTs are completed..
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
tlovieConnect With a Mentor Commented:
You have to also say the column list of in the insert syntax.

SET IDENTITY_INSERT dbo.temp ON;
INSERT INTO dbo.temp (ColA, ColB, ColC.... )
select * from db1.dbo.ppemp
SET IDENTITY_INSERT dbo.temp OFF;

for the whole list of columns on that table.
0
 
rayluvsAuthor Commented:
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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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;
0
 
rayluvsAuthor Commented:
the the columns is a must to include.  Thera many tables and each tables has lots of columns, there is no other way?
0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
No other go while using IDENTITY_INSERT option.
0
 
rayluvsAuthor Commented:
thanx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.