johnkainn
asked on
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?
The tables are identical except that the table that is to be copied to has an identity (incremental) column.
How do I do that?
ASKER
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.
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.
do you want to generate id in target table or get them from the source table?
ASKER
I want to generate id in target table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,
select col1,col2,col3
from tbl_source
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,
select col1,col2,col3
from tbl_source
Thought I had refreshed before posting. Sorry bout that - please see my posting anyway...
insert into target_table
select * from source_table
set identity_insert target_table off