Fabric Data Warehouse – ALTER TABLE workaround

You may be aware that the ALTER TABLE T-SQL command is currently not supported when working with data warehouses in Fabric. This isn’t much of a problem if the table you’re working with is empty, you can simply drop and recreate the table with the new structure.

But if you do have data in a table and you want to make a change to the table, how can you do this without the ALTER TABLE command and without losing your data?

My solution to this is to use the CREATE TABLE AS SELECT (CTAS) T-SQL statement. This statement allows you to insert into a new table while defining the structure of that table. More information on the CTAS statement can be found at https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-develop-ctas.

Suppose you have the following table:

If you wanted to add a new column, YearMonth for example, this CTAS statement would create a new table using the existing table, but with the new column included:

CREATE TABLE [dbo].[Date_New]
AS
SELECT
[Date],
[Day],
[Week],
[Month],
[Quarter],
[Year],
CAST(CONCAT([Year],’-‘,[Month]) AS [varchar](8)) AS [YearMonth]
FROM [dbo].[Date];

You now have a table called dbo.Date_New with the desired columns, at this point you would usually drop the original table and rename the new table to match the name of the original table. But the sp_rename procedure is not supported in Fabric, so what now?

Once you drop the original table, you can use a second, more simple CTAS to create a new table with the original table name:

CREATE TABLE [dbo].[Date]
AS
SELECT
*
FROM [dbo].[Date_New];

You can drop the table dbo.Date_New, and now you’re left with a table called dbo.Date which includes the column YearMonth:

If you have an easier/quicker way to do this then do let me know!

Tags: Data Warehouse, Microsoft Fabric