How to create a copy of a Table using SELECT INTO in SQL Server

To create a copy of a table, you can use a Transact-SQL command SELECT INTO. It will copy all the rows from an existing table into the new table that does not already exist.
Here is an example that will copy the Production.Product table in AdventureWorks Database to a new table called dbo.product_copy (new table will be created with same data types as selected columns in an existing table):

USE AdventureWorks
SELECT * INTO dbo.product_copy FROM Production.Product

You can create the new table using only a subset of columns in the original table.
To copy the contents of Name and ProductNumber columns from Production.Product table in AdventureWorks Database to a new table called dbo.product_name:

USE AdventureWorks
SELECT Name, ProductNumber INTO dbo.product_name FROM Production.Product

If you try to copy rows from one table to an existing table using the same code that we used to copy data to a non existing table:

USE AdventureWorks
SELECT Name, ProductNumber INTO dbo.product_name FROM Production.Product

...you will receive an error:

Read a related articles:

How to insert data from one table to another in SQL Server

How to create a copy of a Table Structure in SQL Server

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">