Tutorial: Clone a table using T-SQL in Microsoft Fabric

  • Article

Applies to: Warehouse in Microsoft Fabric

This tutorial guides you through creating a table clone in Warehouse in Microsoft Fabric, using the CREATE TABLE AS CLONE OF T-SQL syntax.

Create a table clone within the same schema in a warehouse

  1. In the Fabric portal, from the ribbon, select New SQL query.

    Screenshot of the Home screen ribbon, showing where to select New SQL query.

  2. To create a table clone as of current point in time, in the query editor, paste the following code to create clones of the dbo.dimension_city and dbo.fact_sale tables.

    --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];

  3. Select Run to execute the query. The query takes a few seconds to execute.

    Screenshot showing where to select Run to execute your query for table clone.

    After the query is completed, the table clones dimension_city1 and fact_sale1 have been created.

  4. Load the data preview to validate the data loaded successfully by selecting on the dimension_city1 table in the Explorer.

    Screenshot of the Explorer, showing where to find and select the new cloned table dimension_city1.

  5. To create a table clone as of a past point in time, use the AS CLONE OF ... AT T-SQL syntax. The following sample to create clones from a past point in time of the dbo.dimension_city and dbo.fact_sale tables. Input the Coordinated Universal Time (UTC) for the point in timestamp at which the table is required to be cloned.

    CREATE TABLE [dbo].[fact_sale2] AS CLONE OF [dbo].[fact_sale] AT '2024-04-29T23:51:48.923';

CREATE TABLE [dbo].[dimension_city2] AS CLONE OF [dbo].[dimension_city] AT '2024-04-29T23:51:48.923';

  6. Select Run to execute the query. The query takes a few seconds to execute.

    Screenshot showing the T-SQL statements to execute for a table clone at a point in time.

    After the query is completed, the table clones dimension_city2 and fact_sale2 have been created, with data as it existed in the past point in time.

  7. Load the data preview to validate the data loaded successfully by selecting on the fact_sale2 table in the Explorer.

    Screenshot of the Explorer, showing where to find and select the new cloned table fact_sale2.

  8. Rename the query for reference later. Right-click on SQL query 2 in the Explorer and select Rename.

    Screenshot of the Explorer pane in the Fabric portal, showing where to right-click on the query and select Rename.

  9. Type Clone Table to change the name of the query.

  10. Press Enter on the keyboard or select anywhere outside the tab to save the change.

Create a table clone across schemas within the same warehouse

  1. From the ribbon, select New SQL query.

    Screenshot of the Home screen ribbon, showing where to select New SQL query.

  2. Create a new schema within the WideWorldImporter warehouse named dbo1. Copy, paste, and run the following T-SQL code which creates table clones as of current point in time of dbo.dimension_city and dbo.fact_sale tables across schemas within the same data warehouse.

     --Create new schema within the warehouse named dbo1.
CREATE SCHEMA dbo1;

--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. Select Run to execute the query. The query takes a few seconds to execute.

    Screenshot from the Fabric portal query editor showing where to select Run to execute your query for table clone.

    After the query is completed, clones dimension_city1 and fact_sale1 are created in the dbo1 schema.

  4. Load the data preview to validate the data loaded successfully by selecting on the dimension_city1 table under dbo1 schema in the Explorer.

    Screenshot of the Explorer, showing where to find and select the clone created in dbo1 schema.

  5. To create a table clone as of a previous point in time, in the query editor, paste the following code to create clones of the dbo.dimension_city and dbo.fact_sale tables in the dbo1 schema. Input the Coordinated Universal Time (UTC) for the point in timestamp at which the table is required to be cloned.

    --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 '2024-04-29T23:51:48.923';

--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 '2024-04-29T23:51:48.923';

  6. Select Run to execute the query. The query takes a few seconds to execute.

    Screenshot from the Fabric portal query editor showing the query for a cross-schema table clone at a point in time.

    After the query is completed, table clones fact_sale2 and dimension_city2 are created in the dbo1 schema, with data as it existed in the past point in time.

  7. Load the data preview to validate the data loaded successfully by selecting on the fact_sale2 table under dbo1 schema in the Explorer.

    Screenshot from the Fabric portal explorer showing all the new cloned tables created, including dbo1.fact_sale2.

  8. Rename the query for reference later. Right-click on SQL query 3 in the Explorer and select Rename.

    Screenshot of the Explorer pane, showing where to right-click on the query and select Rename.

  9. Type Clone Table in another schema to change the name of the query.

  10. Press Enter on the keyboard or select anywhere outside the tab to save the change.

Next step

Tutorial: Transform data using a stored procedure

Related content