Solved

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

Posted on 2010-09-15
9
341 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

708 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

12 Experts available now in Live!

Get 1:1 Help Now