Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

QUERIES AND SYNTAX

Posted on 2008-10-06
5
Medium Priority
?
179 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

715 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