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

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.

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

Create a table clone within the same schema in a warehouse

1. In the Fabric portal, from the ribbon, 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.

   

   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.

   

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.

   

   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.

   

8. Rename the query for reference later. Right-click on SQL query 2 in the Explorer 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.

   

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.

   

   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.

   

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.

   

   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.

   

8. Rename the query for reference later. Right-click on SQL query 3 in the Explorer 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

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