Copy tables from one database to another in SQL Server

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?

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.

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.

This should work:

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

It will not copy constraints, defaults or indexes. The table created will not have a clustered index.

Alternatively you could:

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

If your destination table exists and is empty.

If it’s one table only then all you need to do is

• Script table definition
• Create new table in another database
• Update rules, indexes, permissions and such
• Import data (several insert into examples are already shown above)

One thing you’ll have to consider is other updates such as migrating other objects in the future. Note that your source and destination tables do not have the same name. This means that you’ll also have to make changes if you dependent objects such as views, stored procedures and other.

Whit one or several objects you can go manually w/o any issues. However, when there are more than just a few updates 3rd party comparison tools come in very handy. Right now I’m using ApexSQL Diff for schema migrations but you can’t go wrong with any other tool out there.

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