Solved

copy from one table to another

Posted on 2008-10-05
7
297 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

785 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