There are various methods for cloning/copying tables with SQL, but not all methods are supported by all RDBMSs.

Below are two options for cloning a table in SQL Server.

Option 1: The SELECT INTO Statement

In SQL Server, we can use the SELECT INTO statement to clone a table and copy its data. The SELECT INTO statement is a Sybase extension that can be used to insert the results of a query into a table in SQL Server. It creates a new table in the default filegroup and inserts the resulting rows from the query into it.

Here’s an example of using the SELECT INTO statement to clone a table and copy its data:

SELECT * INTO Pets2
FROM Pets;

This example creates a table called Pets2 based on the table called Pets and inserts all data from Pets into Pets2.

Note that this does not copy any indexes, constraints, and triggers that are defined in the source table. Those will need to be created separately.

The SELECT...INTO statement operates in two parts – the new table is created, and then rows are inserted. This means that if the inserts fail, they will all be rolled back, but the new (empty) table will remain.

If we don’t want the data to be copied, we can modify our query to return no rows, for example:

SELECT TOP(0) * INTO Pets2
FROM Pets;

Option 2: Generate the CREATE TABLE Script

Another way to do it is to generate the CREATE TABLE script for the table, then change its name (and any references) in the script before running the script. This can usually be done easily via the GUI interface such as SSMS, Azure Data Studio, etc.

For example, we can right-click on the table in the object explorer and select Script Table As... or similar to generate the code.

Here’s an example of the code that Azure Data Studio produces when I right-click on the Pets table and select Script as Create:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Pets](
	[PetId] [int] NOT NULL,
	[PetTypeId] [int] NOT NULL,
	[OwnerId] [int] NOT NULL,
	[PetName] [varchar](60) NOT NULL,
	[DOB] [date] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Pets] ADD PRIMARY KEY CLUSTERED 
(
	[PetId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Pets]  WITH CHECK ADD FOREIGN KEY([OwnerId])
REFERENCES [dbo].[Owners] ([OwnerId])
GO
ALTER TABLE [dbo].[Pets]  WITH CHECK ADD FOREIGN KEY([PetTypeId])
REFERENCES [dbo].[PetTypes] ([PetTypeId])
GO

The good thing about this is that we also have the ALTER TABLE statements for adding in the primary key and foreign keys.

We can take the script and change Pets to Pets2 or whatever we want to name the new table.

Once the clone table has been created, we can then use the INSERT INTO statement to transfer the data from the source table to the destination table.