DROP TABLE IF EXISTS not working on Azure SQL Data Warehouse

Question

I used the SQL Server management studio to generate script against Azure Data Warehouse. I selected Edition Azure Data Warehouse, it generates below the script to drop table if it exists and create table. However, the script cannot pass validation. Please see below for the error message.

DROP TABLE IF EXISTS Table1
GO

Error message:

Parse error at line: 2, column: 12: Incorrect syntax near 'IF'.

Cambyses

What version of SQL server are you using? – Mawkin

Please use the latest version of SSMS learn.microsoft.com/en-us/sql/ssms/… – Packard

Answer 1

As indicated in the documentation, DROP TABLE IF EXISTS is not currently supported in Azure SQL Data Warehouse, as at today:

-- Syntax for SQL Server and Azure SQL Database  
DROP TABLE [ IF EXISTS ] [ database_name . [ schema_name ] . | schema_name . ]  
table_name [ ,...n ]  
[ ; ]

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse    
DROP TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name   
[;] 

Xever

Answer 2

Bob's answer is correct. I tried to add the following as a comment, but it looked terrible without formatting.

You can use the following syntax as an alternative:

if object_id ('dw.dim_location','U') is not null drop table dw.dim_location;

Otilia

Answer 3

Example from Microsoft's documentation:

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
    DROP TABLE #stats_ddl
END

Replace stats_ddl with whatever you named your temp table.

Turning that into a one-liner like Ron's example works too:

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL DROP TABLE #stats_ddl;

Bonin

Answer 4

Just to add another option, the code below should work for SQL Server on premises and Azure SQL DB as well, this is the option when you choose "Script as Drop" from Azure Data Studio:

--
--
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[your_table]') AND type in (N'U'))
DROP TABLE [dbo].[your_table]
--
;

Cyclopean

Out of all answers this is the only one that worked for me on Microsoft Azure SQL Data Warehouse - 10.0.20438.0 – Intersect