Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

QUERIES AND SYNTAX

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
gabepcsolutions
Asked:
gabepcsolutions
  • 3
2 Solutions
 
Patrick MatthewsCommented:
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
 
BrandonGalderisiCommented:
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
 
Patrick MatthewsCommented:
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
 
gabepcsolutionsAuthor Commented:
Could I use the same process to overwrite a whole database from another?
0
 
Patrick MatthewsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now