insert into from one table to another

Question

Post reply

insert into from one table to another

justin.nicholson

Valued Member

Points: 63
More actions
October 27, 2008 at 9:58 am

#383239

I have two databases running on the same SQL server. In each database is an identical table with different contents. I want to copy certain rows from one database and insert the rows into the other database. I'm using the query:
insert into vqsmtest.dbo.ithelp_user select * from vqsmtest2.dbo.ithelp_user where comments like 'Oakmead%'
When I run the query I get the following error:
An explicit value for the identity column in table 'vqsmtest.dbo.ithelp_user' can only be specified when a column list is used and IDENTITY_INSERT is ON.
What does this mean? There is a primary key column in each table that must have a unique entry. How can I get the data copied?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you have to use a list of columns that does not include the primary key, not SELECT *, because the PK already exists:

insert into vqsmtest.dbo.ithelp_user (COL1,COL2,COL3...)

select COL1,COL2,COL3... from vqsmtest2.dbo.ithelp_user

where comments like 'Oakmead%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Or use SET IDENTITY_INSERT ON

In either case, you can't use select *, you need to specify columns at least in the INSERT part of the statement.

Andrew..Peterson SSCertifiable Points: 6838 More actions · Answer 3

Agree, use IDENTITY_INSERT ON, and don't for get to issue the OFF command.

And for the select, if possible, use a where command that ensures that the id's being inserted do not exist, as follows:

set identity_insert target_table ON

insert into target_table (myid, mycolumn)

select myid, mycolumn from sourcedatabase..source_table

where myid not in ( select myid from targetdatabase..target_table)

set identity_insert target_table OFF

The more you are prepared, the less you need it.

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 4

And pray that the IDENTITY column isn't a PK...;)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)