Solved

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

Posted on 2010-09-15
9
354 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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. …
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

740 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