Tip of the Day : How to Copy a Table Using SELECT INTO

Welcome to SQL Server Helper !!!

This site is intended for those who are beginning to use SQL Server as part of their day-to-day activities.  You will find in this site a collection of useful functions, triggers, stored procedures and tips and tricks related to SQL Server.

Should you have any comments or questions regarding this site or if you want to ask SQL Server-related questions, e-mail us here.

We hope we are able to help and we are glad to help!!!

SQL Server Tip of the Day - December 06, 2016

How to Copy a Table Using SELECT INTO

The easiest way to create a table based on the structure and contents of another table is with the use of the SELECT INTO statement:

SELECT * 
INTO [dbo].[Customers_Bkp]
FROM [dbo].[Customers]

This will create a new table called [dbo].[Customer_Bkp] with the same structure as the source table, in this case [dbo].[Customers].  If a column is set to allow NULLs, the same column in the new table will be set to allow NULLs as well.  If a column is an identity column in the source table, it will also be set as an identity column in the new table.  However, this will not copy the default constraint set to any columns in the source table.  It will not also create any indexes, primary key constraints or foreign key constraints on the new table.

If you don’t want to copy the identity column in the new table, you can do the following:

SELECT [CustomerID] * 1 AS [CustomerID], [FirstName], [LastName]
INTO [dbo].[Customers_Bkp]
FROM [dbo].[Customers]

To copy just the table structure without copying the data from the source table to the new table, a WHERE clause can be specified where no records will be returned by the SELECT statement:

SELECT * 
INTO [dbo].[Customers_Bkp]
FROM [dbo].[Customers]
WHERE 1 = 0

To create a new table based on multiple different tables having the same structure, the UNION ALL clause can be added to the SELECT INTO statement:

SELECT *
INTO [dbo].[Customers_US]
FROM [dbo].[Customers_AK]
UNION ALL
SELECT *
FROM [dbo].[Customers_AL]
UNION ALL
SELECT *
FROM [dbo].[Customers_AR]
UNION ALL
SELECT *
FROM [dbo].[Customers_CA]

SQL Server 2012

SQL Server 2008

User-Defined Functions

Date Functions

A collection of useful user-defined functions that deal with dates.

String Functions

A collection of useful user-defined functions that deal with strings (varchar/char/nvarchar/nchar).

Tree Functions

A collection of useful user-defined functions that deal with tree or hierarchical design structures.

Table-Valued Functions

A collection of useful table-valued user-defined functions that can be used to join with other tables.

SQL Server Built-in Functions

A reference to all built-in functions available within SQL Server grouped into categories.

Tips and Tricks

A collection of useful SQL Server-related tips and tricks:

SQL Server Error Messages

A list of SQL Server error messages and for certain error messages, discusses ways on how to solve the error or work around them:

Frequently Asked Questions