SQL Server Copy Table

Copying or replicating tables is one of the crucial functions of database management systems. Copy table is a crucial option to create table data backups or to create duplicate data from a table to another table with few columns or some of the data for various purposes.

In this article, We will learn about the SQL Server Copy Table by understanding various approaches with the help of examples and so on.

SQL Server Copy Table

SQL Server provides a convenient way to copy tables within the same database. Below are the approaches that help us to copy tables in SQL Server are as follows:

1. Using SQL Query
   
2. Using SQL Server Management Studio (SSMS)

Let's set up an Environment:

Let us take an example of the 'Products' table which has the below columns and data to explain the copy table methods:

The above table named 'Products' has 16 rows of data and 6 columns namely ProductID, ProductName, SupplierID, CategoryID, Unit, Price.

1. Using SQL Query

Given below is the Copy Table method using the SQL Query approach using 'SELECT INTO Statement'. This statement copies all columns and data from an existing table to a new table name provided.

Syntax:

SELECT * INTO <NewTableName> FROM <ExistingTableName>

Example: Here the 'ExistingTable' name is the name of the table which exists in the current database selected, and from which a new table copy needs to be created. The 'NewTableName' is the name of the new table where the copy is created.

Query:

SELECT * INTO ProductsNewCopy FROM Products

Output:

Explanation: In the above example the table 'Products' is the existing table and 'ProductsNewCopy' is the new table with all data from 'Products' is created. Above is the Output of the new table 'ProductsNewCopy' which is exactly same as the table 'Products'.

2. Using SQL Server Management Studio (SSMS)

Below are the steps for copying table using the SQL Server Management Studio.

Step 1: Login to Microsoft SQL Server Management Studio

Step 2: Go to the Databases section and select the database from where you like to copy a Table

Step 3: Right click on the selected database to view the popup menu. Select 'Tasks' menu from the popup Menu

Step 4: Select 'Import Data' sub menu from the 'Tasks' menu. The SQL Server Import Export Wizard will be displayed.

Step 5: Press 'Next' button at the bottom of the Import Export Wizard window. The 'Choose a Data Source' window will be displayed.

Step 6: Click on the 'Data Source' combo list box from the 'Choose a Data Source' as shown above.

Step 7: Select the Data source 'SQL Server Native Client 11.0' option. The version number 11.0 can vary based on the SQL Server version.

Input the server name of the SQL Server used when log in.

Step 8: In 'Authentication' section, select the 'Use Windows Authentication'. If the database is logged in using the SQL Server Authentication, then select 'Use SQL Server Authentication' and then input the 'User name' and 'Password' in the respective fields.

Step 9: Select the database name from the list to set the Source Database to copy table. All options for steps 6 to 9 are set in the 'Choose a Data Source' window as shown above.

Step 10: After all required data (Data Source, Server Name, Authentication, Database) in this page is filled, then click the button 'Next>' from the 'Choose a Data Source' window

Step 11: In the next window 'Choose a Destination', select the Destination details like Data Source, Server Name, Authentication and Database (options same as data source window in step 5).

Step 12: In the 'Specify Table Copy or Query' window, select the first option 'Copy data from one or more tables or views' and press 'Next'.

Step 13: In the next window 'Select Source Tables and Views' option, select the table or tables to copy and input the destination table name to copy the selected table.

In this example for source, 'Customers' table is selected and the destination table name 'Customers_SSMS_Copy' is given. Press 'Next' button. The 'Save and Run Package' window will be displayed.

Step 14: Select the 'Run Immediately' option from 'Save and Run Package' screen. Then click 'Next' button. The 'Complete the Wizard' window will be displayed to confirm the options selected.

Press 'Finish' button to run the final step in the wizard to copy the table selected. The 'The Execution was successful' message received if everything was correct.

Step 15: Click the database name and Tables list to view the table which was copied as below.

Conclusion

Overall, Copying tables in SQL Server can be done efficiently using SQL queries or SQL Server Management Studio. Whether you prefer the flexibility of writing your SQL queries or the user-friendly interface of SSMS, SQL Server offers versatile options to duplicate tables, making data management tasks more manageable.