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]