Rename Column in Azure Data Warehouse

Question

I know I can accomplish my objective with this process:

1. Create new column
2. Update new from old
3. Drop old

I am looking for a way to do this with one command. I know that remane object works at the table level. For example, I can do this:

rename object Test.danPatient to dimPatient

But these all fail with various error messages:

rename object Test.dimPatient.City to Test.dimPatient.Town
rename object Test.dimPatient.City to Town
rename object DatabaseName.Test.dimPatient.City to Town
rename object DatabaseName.Test.dimPatient.City to DatabaseName.Test.dimPatient.Town

Plus, according to this, sp_rename is not supported by Azure Data Warehouse. My unsuccessful efforts to use it suggest that the web page is accurate.

Am I attempting the impossible?

Answer 1

Your correct that right now you cannot rename a column in this fashion. The fastest way to do this today is to run a CREATE TABLE AS SELECT (CTAS) operation. Your statement would look something like this:

CREATE TABLE Test.DimPatientNew AS SELECT City [town], <all other columns> FROM Test.DimPatient;

You can add your feedback to our public feedback forum here: https://feedback.azure.com/forums/307516-sql-data-warehouse

Rename a column request: https://feedback.azure.com/forums/307516/suggestions/18434083