How to Copy Table Structure With Or Without Data in SQL Server

Often while working with T-SQL we create copies of tables as a backup of the original/base table so that we can make changes to the data in the original/base table for testing purposes, and revert back to the backup table if needed. SQL Server provides few Key Words or options to perform this operation.

However there are few key differences to note between the original table and the backup table while creating the copies of the tables With/Without data, and these differences could be really important to consider especially when we decide to drop the original table since you have taken the backup. Before you drop the original table, be careful and think twice before you do this! Read through this article to know more about the differences between the Original Table and the Copy of the table.

Now in this article lets take a look at how to create a copy of a table in No Time in either of the following ways:

• Copy Table Structure With Data
• Copy Only Table Structure Without Data

Create a sample table as follows and call it as ExistingEmployeeTable.

 
/* Create Table */
CREATE TABLE ExistingEmployeeTable (
  EmployeeId INT IDENTITY (1,1) NOT NULL,
  EmployeeName NVARCHAR(100) NOT NULL,
  ManagerId INT)
GO

Create a Primary Key on EmployeeId.

/* Create Primary Key */
ALTER TABLE ExistingEmployeeTable
 ADD CONSTRAINT PK_ExistingEmployeeTable_EmployeeId PRIMARY KEY (EmployeeId)
GO

Now create another table called ExistingManagerTable with ManagerId as Primary Key.
 
/* Create Table */
CREATE TABLE ExistingManagerTable (
  ManagerId INT IDENTITY (1,1) NOT NULL,
  ManagerName NVARCHAR(100) NOT NULL)
GO
 
/* Create Primary Key */
ALTER TABLE ExistingManagerTable
 ADD CONSTRAINT PK_ExistingManagerTable_ManagerId PRIMARY KEY (ManagerId)
GO

Now create a Foreign Key table in ExistingEmployeeTable on ManagerId and also create a Non Clustered Index on ManagerId.

/* Create Foreign Key */
ALTER TABLE ExistingEmployeeTable
 ADD CONSTRAINT FK_ExistingEmployeeTable_ManagerId FOREIGN KEY (ManagerId) REFERENCES ExistingManagerTable(ManagerId)
GO

/* Create Non Unique Non Clustered Index */
CREATE NONCLUSTERED INDEX IDX_NU_NCL_ExistingEmployeeTable_ManagerId ON ExistingEmployeeTable(ManagerId)
GO

Now lets take a look at the structure of the two newly created tables as below.

Figure 1.0

Now lets us insert some sample data.
 
INSERT INTO ExistingManagerTable(ManagerName)
SELECT 'John' AS ManagerName
UNION
SELECT 'James' AS ManagerName
UNION
SELECT 'Michael' AS ManagerName
GO

INSERT INTO ExistingEmployeeTable(EmployeeName, ManagerId)
SELECT 'Robert' AS EmployeeName, 1 AS ManagerId
UNION
SELECT 'Daniel' AS EmployeeName, 2 AS ManagerId
UNION
SELECT 'David' AS EmployeeName, 3 AS ManagerId
UNION
SELECT 'Steven' AS EmployeeName, 2 AS ManagerId
UNION
SELECT 'Albert' AS EmployeeName, 3 AS ManagerId
GO

Now lets create a copy of the ExistingEmployeeTable along With Data as follows. You can either create a Physical Table or a Temporary Table based on your needs. 

/* Create Copy of Table With Data */
SELECT *
INTO dbo.NewEmployeeTable
FROM dbo.ExistingEmployeeTable 

You can also create a copy of the ExistingEmployeeTable Without Data as follows. Again here you can create either a Physical Table or a Temporary Table.

/* Create Copy of Table Without Data */
SELECT *
INTO dbo.#NewEmployeeTable
FROM dbo.ExistingEmployeeTable
WHERE 1 = 2

In this query SQL Server creates a copy of the table structure, but since the condition in the WHERE clause is FALSE, the data will not be copied to the newly created table.

Now lets take a look at the structure of the backup/newly created NewEmployeeTable table.

Figure 2.0

Now compare the table structures of ExistingEmployeeTable (Old Table) & NewEmployeeTable (New Table), and here are few of the key differences to note:

1. Primary & Foreign Keys are not created in the Newly Created Table (NewEmployeeTable)
2. Indexes are not created in the Newly Created Table (NewEmployeeTable)
3. Default/Check Constraints are not created in the Newly Created Table (NewEmployeeTable)

These are few most important differences which one should note before deciding to drop the original table after taking a backup of the table using SELECT * INTO statement.