copy from one table to another

I want to copy everything from one table to another.
The tables are identical except that the table that is to be copied to has an identity (incremental) column.
How do I do that?
johnkainnAsked:
Who is Participating?
 
momi_sabagCommented:
ok
so just use
insert into target_table
select * from source_table
0
 
momi_sabagCommented:
set identity_insert target_table on
insert into target_table
select * from source_table
set identity_insert target_table off
0
 
johnkainnAuthor Commented:
I have a target table with an Id column that is identity column. The name of the target table is y_B.
If I try to use the code you sent I get following error:

Explicit value must be specified for identity column in table 'y_B' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.


0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
momi_sabagCommented:
do you want to generate id in target table or get them from the source table?
0
 
johnkainnAuthor Commented:
I want to generate id in target table.
0
 
Mark WillsTopic AdvisorCommented:
If the only difference between source table and destination table is an additional idenity column in the destination, then you will need to specify the column names...

e.g.  say tbl_destination (id int identity, col1 varchar,col2 int, col3 datetime) and tbl_source (col1 varchar,col2 int, col3 datetime)

then you do...

insert tbl_destination(col1,col2,col3)
select col1,col2,col3
from tbl_source

0
 
Mark WillsTopic AdvisorCommented:
Thought I had refreshed before posting. Sorry bout that - please see my posting anyway...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.