Solved

How to solve ' identity column' SQL Message in MsSql 2000 when copying table to another

Posted on 2010-09-15
9
351 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:rayluvs
  • 4
  • 4
9 Comments
 

Author Comment

by:rayluvs
ID: 33684039
Ok, I found it:

               SET IDENTITY_INSERT tablename ON
               SET IDENTITY_INSERT tablename OFF

But it won't set it off, why?
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 375 total points
ID: 33684061
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
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 375 total points
ID: 33684066
>> But it won't set it off, why?

You need to turn it off manually once the INSERTs are completed..
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 7

Assisted Solution

by:tlovie
tlovie earned 125 total points
ID: 33684176
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
 

Author Comment

by:rayluvs
ID: 33684396
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33687466
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
 

Author Comment

by:rayluvs
ID: 33687514
the the columns is a must to include.  Thera many tables and each tables has lots of columns, there is no other way?
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 375 total points
ID: 33687525
No other go while using IDENTITY_INSERT option.
0
 

Author Comment

by:rayluvs
ID: 33689323
thanx
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question