Copy tables from one database to another in SQL Server.

Question

I have a database called foo and a database called bar. I have a table in foo called tblFoobar that I want to move (data and all) to database bar from database foo. What is the SQL statement to do this?

Answer 1

On SQL Server? and on the same database server? Use three part naming.

INSERT INTO bar..tblFoobar( fieldlist ) SELECT fieldlist FROM foo..tblFoobar

This just moves the data. If you want to move the table definition (and other attributes such as permissions and indexes), you'll have to do something else.

Answer 2

This should work:

SELECT * 
INTO DestinationDB..MyDestinationTable 
FROM SourceDB..MySourceTable

Answer 3

1. Script the 'create table' in management studio, run that script in bar to create the table. (Right click table in object explorer, script table as, create to...)

2. INSERT bar.[schema].table SELECT * FROM foo.[schema].table

Answer 4

SQL Server Management Studio's "Import Data" task (right-click on the DB name, then tasks) will do most of this for you. Run it from the database you want to copy the data into.

If the tables don't exist it will create them for you, but you'll probably have to recreate any indexes and such. If the tables do exist, it will append the new data by default but you can adjust that (edit mappings) so it will delete all existing data.

I use this all the time and it works fairly well.

Answer 5

You should try this:

Select * into foo..tblFoobar from bar..tblFoobar

You just run that command. This will work and copy all the data of Foobar data from Foo to Bar database.

Answer 6

What if we want to copy all the data of a particular table to another table of the different DB? (we are not copying table, just values of all the table fields). How we do that?

EG: Database1>db1Table1>db1tb1Fields copy to Database2>db2Table1>db2tb1Fields

Answer 7

In that case you would just do:

INSERT INTO Database2..Table1 SELECT * FROM Database1..Table1