Solved

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

Posted on 2010-09-15
9
348 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL tables, .net application and ETL connundrum 3 53
Showing random records from database 10 35
SQL Insert parts by customer 12 31
always on switch back after failover 2 31
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

816 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now