Parse_Syntax_Error Help

[melodiesd]

Hello all, 

I'm new to Databricks and can't figure out why I'm getting an error in my SQL code.

Error in SQL statement: ParseException: [PARSE_SYNTAX_ERROR] Syntax error at or near 'if'.(line 1, pos 0) == SQL == if OBJECT_ID('tempdb.#InitialData') IS NOT NULL ^^^ DROP TABLE #InitialData

We run this code at my organization in Microsoft SQL so I know that code itself works, so why am I getting this error when I try to run it in a Notebook? 

[Kaniz]

Hi @melodiesd, You're encountering an error because Databricks SQL is based on Spark SQL, which does not support the same syntax as Microsoft SQL Server.

The specific command you're trying to run, if OBJECT_ID('tempdb.#InitialData') IS NOT NULL DROP TABLE #InitialData, is a T-SQL command (Transact-SQL, used in Microsoft SQL Server) that checks if a temporary table exists and drops it if it does. 

Spark SQL, and by extension Databricks SQL, does not recognize this syntax. In Spark SQL, you would need to use different methods to check if a table exists and to drop it. 

For example, to drop a table in Spark SQL, you can use the DROP TABLE IF EXISTS command:

sql
DROP TABLE IF EXISTS tableName

This command will drop the table if it exists and do nothing if it doesn't, effectively accomplishing the same goal as your original T-SQL command.

However, please note that temporary tables in Spark SQL are handled differently than in SQL Server. You must understand how Spark takes tables and databases to translate your SQL Server code to Spark SQL effectively.