Solved

copy records from one identical table to another identical table on two different datbases

Posted on 2013-06-25
5
298 Views
Last Modified: 2013-06-26
I have database1 with tblAttendance.   I also have database2 with tblAttendance.  There is a column in the table called Redetermination which determines which records will be migrated to the new database and table.

I would like to transfer all records from dbo.database1.tblAttendance where tblAttendance.Redetermination = 1   to dbo.database2.tblAttendance

How can I do that using SQL statments.  Unless there is also a wizard that does this function...
0
Comment
Question by:al4629740
  • 2
  • 2
5 Comments
 
LVL 12

Expert Comment

by:duttcom
ID: 39276813
Try this -

SELECT *
INTO dbo.database2.tblAttendance
FROM dbo.database1.tblAttendance
WHERE dbo.database1.tblAttendance.Redetermination = '1'
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 39276825
Actually SELECT ... INTO won't work in this instance.

SELECT INTO creates a new table.  If the destination table already exists, you'll get an error.

You'd want to do something more like this:

INSERT INTO database2.dbo.tblAttendance
SELECT * from database1.dbo.tblAttendance
where database1.dbo.tblAttendance.Redetermination = 1
0
 
LVL 12

Expert Comment

by:duttcom
ID: 39276878
My bad! sjwales is spot-on , sorry for the confusion.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39276907
EDIT: Oops, replied to wrong Question :)
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 39277016
INSERT INTO database2.dbo.tblAttendance(field1, field2, field3,Redetermination)
     SELECT field1, field2, field3 , 1 as Redetermination
     FROM database1.dbo.tblAttendance
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now