tutorial-clone-table.md

title	Data Warehouse Tutorial: Clone a Table with T-SQL in Warehouse
description	In this tutorial, learn how to clone a table with T-SQL.
ms.reviewer	scbradl, prlangad
ms.date	04/06/2025
ms.topic	tutorial
ms.search.form	Warehouse Clone table

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

Applies to: [!INCLUDE fabric-dw]

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

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.

   :::image type="content" source="media/tutorial-clone-table/ribbon-new-sql-query.png" alt-text="Screenshot of the New SQL query option on the ribbon." lightbox="media/tutorial-clone-table/ribbon-new-sql-query.png":::

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.

   :::image type="content" source="media/tutorial-clone-table/run-to-execute.png" alt-text="Screenshot of the Run option on the query editor ribbon.":::

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

   :::image type="content" source="media/tutorial-clone-table/explorer-select-table.png" alt-text="Screenshot of the Explorer pane, highlighting the dimension city 1 table.":::

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 creates 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 creates 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

[!div class="nextstepaction"] 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