Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

copy from one table to another

Posted on 2008-10-05
7
Medium Priority
?
303 Views
Last Modified: 2010-03-19
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?
0
Comment
Question by:johnkainn
  • 3
  • 2
  • 2
7 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22644014
set identity_insert target_table on
insert into target_table
select * from source_table
set identity_insert target_table off
0
 

Author Comment

by:johnkainn
ID: 22644056
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22644058
do you want to generate id in target table or get them from the source table?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:johnkainn
ID: 22644862
I want to generate id in target table.
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 22644898
ok
so just use
insert into target_table
select * from source_table
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22645508
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22645514
Thought I had refreshed before posting. Sorry bout that - please see my posting anyway...
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question