Forum Discussion
Alter Table in Fabric
I am trying to drop a column through SQL endpoints using the ALTER table command but it errors out with a message : "Specific statement is not suported"
Please note I am trying to alter a table in a datawarehouse and not in a lakehouse.
11 Replies
I don't think it's possible. I'm not sure what is the workaround. Perhaps create a new table without that column, and copy the data into the new table.
https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area#limitations
https://www.serverlesssql.com/the-reality-of-alter-table-in-fabric-warehouses-2/
This is a bit shocking that such a basic SQL command isnt supported.
The semantic model is partially developed that involves this particular table. Do you think there would be any checks in fabric that would prevent dropping of the table if the table is already referred somewhere else ?I don't think there will be anything preventing you from dropping the table, even if the table is referred somewhere else. So that would probably cause breaking of the semantic model.
I'm not sure what happens if you drop and then recreate the table. Will the semantic model work then? I don't know.
I hope someone else knows the answer and can tell.
Hi SachinNandanwar,
Can you please share some more detailed information about these operations? They should help us clarify your scenario and test to troubleshoot.
For example:
Are you directly executed these on the data warehouse Editor or use external tools to connect to the data warehouse?
Any reference on the table from other workflows or features? Did this table add to the semantic model?Regards,
Xiaoxin Sheng
Hi Anonymous ,
I am not using any external tools.We have a redudant column on a table which needs to be dropped.Executing ALTER TABLE...DROP COLUMN is erroring out.
Now this table is being referenced in the semantic model and we are not sure if dropping the table and recreating it with the necessary columns would break the existing model.Hi SachinNandanwar,
OK, I test these operations on my side, and they also be blocked due to the limits. For your scenario, perhaps you can try to remove the not need columns at the 'data ingestion' steps.
Regards,
Xiaoxin Sheng
