"Identity" issues 1

[manmaria]

I am copying the data from one data base to another database with similar table structures. One table has an identity column, and I am able to copy the data with IDENTITY_INSERT on but the problem is I need to mention all the column names in the insert statment.

Is there any way I can copy data from one table to another without mentioning any column names, since the table strucutes are same.

Right now I am doing like this

SET IDENTITY_INSERT NEWDB.table1 ON
INSERT INTO NEWDB.blah1
(ID,col1,col2)
select ID,col1,col2 from OLDDB.blah1

but if I like to do something like this

Insert into NEWDB.blah1
select * from OLDDB.blah1

Any ideas?

Thanks,

 

[mrdenny]

Nope.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[gmmastros]

I think you will need to write out the column names.

SQLDenis originally showed me this trick about a year ago.

Open Query Analyzer
drill down to the table you want to copy.
Click the + symbol
drag the Columns folder to a QA window.

All the columns will be listed out for you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

Hey, that is a neat trick! Thanks, gmmastros! Purple star-like thing for you.

@=)





Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[RobertT687]

You could also right click on the table in QA and choose one of the 'Script Object to New Window As..' options, then copy and paste what you need.