There are several ways to make a copy of database table in MS SQL Server. I write down some ways to make a copy of table. Which way to choose is your choice according to table structure, indexes and your wish to make something manually.

1. Manual copy of database table

In Micrisoft SQL Management Studio you need to choose database, table, tight click and choose from menu "Script Table as" -> "CREATE TO" -> "New Query Editor Window" (Figure 1). In query window there will be a code of table structure. With indexes. In this code you need to enter new database name or new table name.

Figure 1. Create a copy of table structure

Figure 1. Create a copy of table structure

Using this method indexes will be created, but not triggers. Triggers you should copy manually in the way like this.

To copy data from old table to the new already created one, you can use SQL query like this:

INSERT into [db2].[dbo].tmp_tbl_Deps SELECT * FROM [db1].[dbo].tbl_Deps

2. Copy SQL table with a one line SQL query

Copy database table structure and data in one database:

SELECT * into tmp_tbl_Dep FROM tbl_Deps

Copy table and it's content from one database to antother:

SELECT * into [Db2].[dbo].tmp_tbl_Deps FROM [Db1].[dbo].tbl_Deps

The negative side of this solution is no indexes in new tables. But in my humble opinion, indexes are important in productive environments, in developer environment you can skip them sometimes.

3. Copy SQL table from a database server to another one

You can do it with Linked server. At first, connect to the destination database and add a linked server.

sp_addlinkedserver 'remote_dbserver'

Then execute the script like this:


SET IDENTITY_INSERT [current_db_name].[dbo].currenttable OFF;
insert into [current_db_name].[dbo].currenttable ([Field1], [Field2], [Field3], [Field4])
select [Field1], [Field2], [Field3], [Field4]
from [remote_dbserver].[remote_db_name].[dbo].remotetable

SET IDENTITY_INSERT  [current_db_name].[dbo].currenttable  ON;