Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

QUERIES AND SYNTAX

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

564 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