CODEWITHSUNDEEP
t-sqlsql-server-2008-r2ddl
DataWrangler
DataWrangler

Reputation: 2165

IF Exists doesn't seem to work for a Table Drop if already exists

Was getting this error each and every time tried to execute a DROP Table if exists

Step 1: Created a Table 

CREATE TABLE Work_Tables.dbo.Drop_Table_Test (RowID INT IDENTITY(1,1), Data VARCHAR(50))

INSERT INTO Work_Tables.dbo.Drop_Table_Test 
SELECT 'Test' UNION
SELECT 'Test1' UNION
SELECT 'Test2' UNION
SELECT 'Test3'

Step 2: Wrote a IF Exists block to check if the Table exists.

IF EXISTS (SELECT 1 FROM Work_Tables.dbo.SysObjects WHERE NAME LIKE  'Drop_Table_Test' AND XType = 'U')
BEGIN
 PRINT 'IN'
 DROP TABLE Work_Tables.dbo.Drop_Table_Test
END

CREATE TABLE Work_Tables.dbo.Drop_Table_Test (RowID INT IDENTITY(1,1), Data VARCHAR(50), NAME VARCHAR(20), PreCheck INT)


INSERT INTO Work_Tables.dbo.Drop_Table_Test (Data, Name, PreCheck)
SELECT 'Test','SRK',1 UNION
SELECT 'Test1','Daya',2 UNION
SELECT 'Test2','Dinesh',3 UNION
SELECT 'Test3','Suresh',4

On running the Step 2 Code its obvious the Table has to be Dropped and recreated with the same name but it didn't even enter the Begin End block. enter image description here

I feel that its because have added few more columns in the second try, but still not clear why it should have problems as we are to DROP the table.

Upvotes: 5

Views: 6348

Answers (2)

S3S
S3S

Reputation: 25112

You can not drop and create the same table in the same batch in SQL Server.

Break your code up into separate batches so the table can be dropped before you try and recreate it. Add GO after END in your BEGIN / END statement.

IF EXISTS (SELECT 1 FROM Work_Tables.dbo.SysObjects WHERE NAME LIKE  'Drop_Table_Test' AND XType = 'U')
BEGIN
 PRINT 'IN'
 DROP TABLE Work_Tables.dbo.Drop_Table_Test
END
GO   --Add this...
....

Straight from Microsoft's Documentation:

DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.

Upvotes: 4

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

You can try to use this syntax:

IF OBJECT_ID('dbo.Drop_Table_Test', 'U') IS NOT NULL 
  DROP TABLE dbo.Drop_Table_Test;

IF EXISTS will drop the table only when your table Drop_Table_Test does not contain any row. In case if it contains the data then it will not drop the table.

Upvotes: 0

Related Questions