How to Copy a Table

There are few methods of copying a table to another database, depending on your situation.

Same SQL Server Instance

If trying to copy a table to a database that is on the same instance of SQL Server, The easiest solution is to use a SELECT INTO while using the fully qualifed database names.

[cc lang=”sql”]
SELECT *
INTO Database2.dbo.TargetTable
FROM Database1.dbo.SourceTable
[/cc]

This will create a copy of the entire table across two different databases and will definitely be the quickest way. This method utilizes minimal inserts. The caveat is that it will not copy over the primary keys, foreign keys or the indexes. These will have to be scripted out and added after the insert.

Different SQL Server Instance (and/or different server)

If trying to copy a table to a different instance (be it on the same server or not), there are two basic routes.

OPENROWSET is an easy way to do the insert. Just replace the server names below and instance name (if the database is defined by an instance).

[cc lang=”sql”]
SELECT *
INTO dbo.TargetTable
FROM OPENROWSET(‘SQLNCLI’, ‘server=SourceServerNameSourceInstance;trusted_connection=yes’,
‘SELECT * FROM SourceDatabase.dbo.SourceTable’)
[/cc]

The other way is to utilize the SSIS Import/Export Wizard. This always works well too. In order to initialize, right click on the database, select tasks, then import/export.