Solved

copy from one table to another

Posted on 2008-10-05
7
300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

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

Accepted Solution

by:
momi_sabag earned 125 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

627 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