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

x
?
Solved

QUERIES AND SYNTAX

Posted on 2008-10-06
5
Medium Priority
?
180 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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 800 total points
ID: 22652310
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 1200 total points
ID: 22652763
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 93

Expert Comment

by:Patrick Matthews
ID: 22652794
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
ID: 22653298
Could I use the same process to overwrite a whole database from another?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22653424
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

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