Copy table from one database to another

Question

I've just created an empty database on my machine. I now wish to copy a table from our server database to this local database.

What sql commands do I need to run to do this? I wish to create the new table, copy data from the old table and insert it into the new table.

Answer 1

Create a linked server to the source server. The easiest way is to right click "Linked Servers" in Management Studio; it's under Management -> Server Objects.

Then you can copy the table using a 4-part name, server.database.schema.table:

select  * into    DbName.dbo.NewTable from    LinkedServer.DbName.dbo.OldTable 

This will both create the new table with the same structure as the original one and copy the data over.

Answer 2

Assuming that they are in the same server, try this:

SELECT *
INTO SecondDB.TableName
FROM FirstDatabase.TableName

This will create a new table and just copy the data from FirstDatabase.TableName to SecondDB.TableName and won't create foreign keys or indexes.

Answer 3

Another method that can be used to copy tables from the source database to the destination one is the SQL Server Export and Import wizard, which is available in SQL Server Management Studio.

You have the choice to export from the source database or import from the destination one in order to transfer the data.

This method is a quick way to copy tables from the source database to the destination one, if you arrange to copy tables having no concern with the tables’ relationships and orders.

When using this method, the tables’ indexes and keys will not be transferred. If you are interested in copying it, you need to generate scripts for these database objects.

If these are Foreign Keys, connecting these tables together, you need to export the data in the correct order, otherwise the export wizard will fail.

Feel free to read more about this method, as well as about some more methods (including generate scripts, SELECT INTO and third party tools) in this article: https://www.sqlshack.com/how-to-copy-tables-from-one-database-to-another-in-sql-server/