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

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 2

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 3

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.

Answer 4

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.

Answer 5

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 6

You can also use the Generate SQL Server Scripts Wizard to help guide the creation of SQL script's that can do the following:

• copy the table schema
• any constraints (identity, default values, etc)
• data within the table
• and many other options if needed

Good example workflow for SQL Server 2008 with screen shots shown here.

Answer 7

You may go with this way: ( a general example )

insert into QualityAssuranceDB.dbo.Customers (columnA, ColumnB)
Select columnA, columnB from DeveloperDB.dbo.Customers

Also if you need to generate the column names as well to put in insert clause, use:

    select (name + ',') as TableColumns from sys.columns 
where object_id = object_id('YourTableName')

Copy the result and paste into query window to represent your table column names and even this will exclude the identity column as well:

    select (name + ',') as TableColumns from sys.columns 
where object_id = object_id('YourTableName') and is_identity = 0

Remember the script to copy rows will work if the databases belongs to the same location.

---

You can Try This.

select * into <Destination_table> from <Servername>.<DatabaseName>.dbo.<sourceTable>

Server name is optional if both DB is in same server.

Answer 8

I give you three options:

If they are two databases on the same instance do:

SELECT  * INTO My_New_Table FROM [HumanResources].[Department];

If they are two databases on different servers and you have linked servers do:

SELECT  * INTO My_New_Table FROM [ServerName].[AdventureWorks2012].[HumanResources].[Department];

If they are two databases on different servers and you don't have linked servers do:

SELECT * INTO My_New_Table
FROM OPENROWSET('SQLNCLI', 'Server=My_Remote_Server;Trusted_Connection=yes;',
     'SELECT * FROM AdventureWorks2012.HumanResources.Department');