IDENTITY_INSERT ON problem

Question

Post reply

IDENTITY_INSERT ON problem

nishant.sagar

Mr or Mrs. 500

Points: 503
More actions
November 26, 2008 at 2:12 pm

#75373

I am using the following query in sql server 2005
1)
SET IDENTITY_INSERT NewDataBase.Extensions.FieldSelectionGroups ON
GO
INSERT INTO NewDataBase.Extensions.FieldSelectionGroups
SELECT *
FROM OldDataBase.Extensions.FieldSelectionGroups with (HOLDLOCK TABLOCKX)
GO
SET IDENTITY_INSERT NewDataBase.Extensions.FieldSelectionGroups OFF
GO
2)
SET IDENTITY_INSERT NewDataBase.Extensions.FieldSelectionGroups ON
GO
INSERT INTO NewDataBase.Extensions.FieldSelectionGroups
SELECT *
FROM OldDataBase.Extensions.FieldSelectionGroups
GO
SET IDENTITY_INSERT NewDataBase.Extensions.FieldSelectionGroups OFF
GO
but getting following error
An explicit value for the identity column in table 'NewDataBase.Extensions.FieldSelectionGroups' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Please help
-Nishant

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

The message is telling you the fix: you cannot use

Insert MyTable

Select *

.....

You need to explicitly list out the columns you want to insert into. In other words:

Insert MyTable(colA, colB, colC, colD)

Select colA, colB, colC, colD

from .....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hi Nishant

It's exactly as your error message states, you can't use

INSERT INTO NewDataBase.Extensions.FieldSelectionGroups

SELECT *

FROM OldDataBase.Extensions.FieldSelectionGroups

you have to use

INSERT INTO NewDataBase.Extensions.FieldSelectionGroups (column1, column2 etc)

SELECT column1, column2 etc

FROM OldDataBase.Extensions.FieldSelectionGroups

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nishant.sagar Mr or Mrs. 500 Points: 503 More actions · Answer 3

Thanks for all responses but my problem is that I am creating a script to import data for all tables of the database for extension schema.In that script its very difficult to mention all column name.

So please help me if any other way out is there without specifying column name.

Very Thanks,

Nishant

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

nishant.sagar (11/26/2008)
Thanks for all responses but my problem is that I am creating a script to import data for all tables of the database for extension schema.In that script its very difficult to mention all column name.
So please help me if any other way out is there without specifying column name.
Very Thanks,
Nishant

In that case - take a look at building the insert statements dynamically, based on the column names you find the the sys.all_columns view for that particular table. Then set that up to run through something like sp_msforeachtable, or build something to go through sys.tables for the table names.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

nishant.sagar Mr or Mrs. 500 Points: 503 More actions · Answer 5

nishant.sagar

Mr or Mrs. 500

Points: 503

November 26, 2008 at 5:16 pm

#904095

Thanks Matt that works well for me:)

-Nishant

Steve Holle SSC-Addicted Points: 466 More actions · Answer 6

Why can't select * be used when insert_identity is on? You could use:

select *

into dest_table

from sourc_table

and that would work, right?

fsyang Valued Member Points: 57 More actions · Answer 7

Please try to list out all the fields including the identity column explicitly

For example:

SET IDENTITY_INSERT table1 ON

insert table1 (field1,field2,field3...)

select * from table2

SET IDENTITY_INSERT table1 OFF

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Please note: 2 year old thread

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass