I am working with the version v.17.1 of the SQL Server Management Studio. And theoretically, the query DROP TABLE IF EXISTS
works from SQL Server 2016.
You can see this in the official web page of Microsoft: DROP TABLE
However, when I try to execute the query
DROP TABLE IF EXISTS dbo.EmpLocations;
an error appears:
Incorrect syntax near the keyword 'IF'.
What is the problem? I'm using a specific database of Microsoft to work with SQL Server 2016 - 2017.
for backwards compatibility with all versions of sql-server, use this:
IF OBJECT_ID('dbo.emplocations') IS NOT NULL
DROP TABLE dbo.emplocations;
GO
We have many ways to do this but generally it's not a practice to DROP
and CREATE
the physical tables you can use temporary tables instead
Check Existence of real table and DROP:
IF EXISTS(SELECT 1 FROM sys.objects where name = 'myTable' AND type = 'U')
DROP TABLE dbo.myTable
IF OBJECT_ID('dbo.myTable', 'U') IS NOT NULL
DROP TABLE dbo.myTable;
Check existence of temporary table and DROP:
IF OBJECT_ID('tempdb.dbo.#myTable', 'U') IS NOT NULL
DROP TABLE #myTable;
Finally, It was my mistake :(
As other people have said, I was using the SQL Server Management v.17.1; however, I was working yet with the version of SQL SERVER 2014. I began to install the new SQL 2016, but I only installed the SSMS17, and not the SQL Server 2016 itself.
Now, I have installed correctly the version of SQL SERVER 2016. And I can execute the query DROP TABLE IF EXISTS Sales.Orders2;
without any problem!
If I go to the version (SELECT @@VERSION
), I can see that I have the correct installed: Microsoft SQL Server 2016 (SP1-GDR) (KB4019089) - 13.0.4206.0 (X64) Jul 6 2017 07:55:03 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows 10 Home 6.3 (Build 15063: )
And remember that the new version of SQL2016 they have introduced this new way to drop tables https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With