Tutorial: Clone a table with T-SQL in a Warehouse

Applies to: ✅ Warehouse in Microsoft Fabric

In this tutorial, learn how to clone a table with T-SQL. Specifically, you learn how to create a table clone with the CREATE TABLE AS CLONE OF T-SQL statement.

Note

This tutorial forms part of an end-to-end scenario. In order to complete this tutorial, you must first complete these tutorials:

1. Create a workspace
2. Create a Warehouse
3. Ingest data into a Warehouse
4. Create tables with T-SQL in a Warehouse

A cloned table provides several benefits:

• You can use the CREATE TABLE AS CLONE OF T-SQL statement to create a table clone at the current point-in-time or at a previous point-in-time.
• You can clone tables in the Fabric portal. For examples, see Tutorial: Clone tables in the Fabric portal.
• You can query data in a Warehouse as it appeared in the past by using a SELECT statement with the OPTION clause. For more information, see Query data as it existed in the past.

Clone a table within the same schema

In this task, learn how to clone a table within the same schema in the warehouse.

1. Ensure that the workspace you created in the first tutorial is open.

2. In the Wide World Importers warehouse, on the Home ribbon, select New SQL query.

   

3. In the query editor, paste the following code. The code creates a clone of the dimension_city table and the fact_sale table.

    --Create a clone of the dbo.dimension_city table.
    CREATE TABLE [dbo].[dimension_city1] AS CLONE OF [dbo].[dimension_city];
   
    --Create a clone of the dbo.fact_sale table.
    CREATE TABLE [dbo].[fact_sale1] AS CLONE OF [dbo].[fact_sale];
   

4. To execute the query, on the query designer ribbon, select Run.

   

5. When the execution completes, to preview the loaded data, in the Explorer pane, select dimension_city1.

   

6. To create a table clone as of a past point in time, in the query editor, paste the following code to replace the existing statements. The code creates a clone of the dimension_city table and the fact_sale table at a certain point in time.

    --Create a clone of the dbo.dimension_city table at a specific point in time.   
   CREATE TABLE [dbo].[dimension_city2] AS CLONE OF [dbo].[dimension_city] AT '2025-01-01T10:00:00.000';
   
    --Create a clone of the dbo.fact_sale table at a specific point in time.
   CREATE TABLE [dbo].[fact_sale2] AS CLONE OF [dbo].[fact_sale] AT '2025-01-01T10:00:00.000';
   

   Important

   You should replace the timestamp with a past date that is within 30 days of today, but after the date and time (in Coordinated Universal Time—UTC) that you completed the Ingest data into a Warehouse tutorial.

7. Run the query.

8. When execution completes, preview the data loaded into the fact_sale2 table.

9. Rename the query as Clone Tables.

Clone a table across schemas within the same warehouse

In this task, learn how to clone a table across schemas within the same warehouse.

1. To create a new query, on the Home ribbon, select New SQL query.

2. In the query editor, paste the following code. The code creates a schema, and then create a clone of the fact_sale table and the dimension_city table in the new schema.

    --Create a new schema within the warehouse named dbo1.
    CREATE SCHEMA dbo1;
    GO
   
    --Create a clone of dbo.fact_sale table in the dbo1 schema.
    CREATE TABLE [dbo1].[fact_sale1] AS CLONE OF [dbo].[fact_sale];
   
    --Create a clone of dbo.dimension_city table in the dbo1 schema.
    CREATE TABLE [dbo1].[dimension_city1] AS CLONE OF [dbo].[dimension_city];
   

3. Run the query.

4. When execution completes, preview the data loaded into the dimension_city1 table in the dbo1 schema.

5. To create table clones as of a previous point in time, in the query editor, paste the following code to replace the existing statements. The code create a clone of the dimension_city table and the fact_sale table at certain points in time in the new schema.

   --Create a clone of the dbo.dimension_city table in the dbo1 schema.
   CREATE TABLE [dbo1].[dimension_city2] AS CLONE OF [dbo].[dimension_city] AT '2025-01-01T10:00:00.000';
   
   --Create a clone of the dbo.fact_sale table in the dbo1 schema.
   CREATE TABLE [dbo1].[fact_sale2] AS CLONE OF [dbo].[fact_sale] AT '2025-01-01T10:00:00.000';
   

   Important

   You should replace the timestamp with a past date that is within 30 days of today, but after the date and time (in UTC) that you completed the Ingest data into a Warehouse tutorial.

6. Run the query.

7. When execution completes, preview the data loaded into the fact_sale2 table in the dbo1 schema.

8. Rename the query as Clone Tables Across Schemas.

Next step

Tutorial: Transform data with a stored procedure in a Warehouse

Related content

• Clone table in Microsoft Fabric
• Tutorial: Clone tables in the Fabric portal
• CREATE TABLE AS CLONE OF