Solved

QUERIES AND SYNTAX

Posted on 2008-10-06
5
171 Views
Last Modified: 2012-05-05
How do I copy a table from one database to another:

SELECT
FROM
WHERE

...how do I compare data from a field in a table with a table and a field in a another database.
0
Comment
Question by:gabepcsolutions
  • 3
5 Comments
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 200 total points
Comment Utility
gabepcsolutions said:
>>How do I copy a table from one database to another:

SELECT *
INTO Database2.dbo.NewTableName
FROM Database1.dbo.OldTableName

>>...how do I compare data from a field in a table with a table and a field in a another database.

If on the same server...

SELECT a.*, b.*
FROM Database1.dbo.SomeTable a INNER JOIN
    Database2.dbo.SomeTable b ON a.ID = b.ID
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 300 total points
Comment Utility
If you want to copy into an existing table (ie. you already created the destination table):

insert into NewDb.dbo.NewTable
select * from OldDb.dbo.OldTable


If you need to create the table, I would recommend using SSMS (sql server management studio) to generate the SQL script for the table.  That way, all of your constraints are and indexes copied over.  To do this, right click the table, script table as > create to > new query editor window.... then change your DB name at the top

Then, use the SQL above.

If your table has an identity column, and you want to keep the value, you will need to change a few things:

from the NEW db, run this:

set identity_insert newTable on
insert into NewDb.dbo.NewTable  (Field1,field2,field3...) --listing your actual field names
select field1,field2,field3.....
from OldDb.dbo.OldTable

set identity_insert newTable off
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
gabepcsolutions,

Brandon makes a great point: my simple SQL will create the new table with the same data and column data types,
but it will not bring over indexes, constraints, triggers, etc.

Regards,

Patrick
0
 

Author Comment

by:gabepcsolutions
Comment Utility
Could I use the same process to overwrite a whole database from another?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
gabepcsolutions said:
>>Could I use the same process to overwrite a whole database from another?

In that case, I would just backup Database1, and then restore the backup over Database2.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

728 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

14 Experts available now in Live!

Get 1:1 Help Now