How to DROP and CREATE a table in SQL Server 2016 Stored Procedure

Question

I am trying to create a stored procedure that will drop a table and then create a new table with the same name.

However, when I right clicked on the table and did Script Table as ... DROP and CREATE To ... New Query Editor Window and then copied the script into a blank stored procedure, it wouldn't let me create the procedure because

There is already an object named 'MyTable' in the database

What should I do?

NOTE: the new table will not be the same structure as the old table. I make some transformations using other stored procedures to the table, so at the end, I want to drop it and create from scratch.

Here is the actual script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spDropAndCreate]
AS
BEGIN
    SET NOCOUNT ON;

    IF OBJECT_ID ('dbo.MyTable','U') IS NOT NULL
         DROP TABLE [dbo].[MyTable]

    CREATE TABLE [dbo].[MyTable]
    (
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Col1] [nvarchar](50) NULL,
        [Col2] [nvarchar](50) NULL,
        [Col3] [nvarchar](50) NULL,
        [Col4] [nvarchar](50) NULL,
        [Col5] [nvarchar](50) NULL,
        [Col6] [nvarchar](50) NULL,
        [Col7] [nvarchar](50) NULL,
        [Col8] [nvarchar](50) NULL,
        [Col9] [nvarchar](50) NULL,
        [Col10] [nvarchar](50) NULL
    )
    GO
END

Here is the error message:

Msg 102, Level 15, State 1, Procedure spDropAndCreate, Line 17 [Batch Start Line 9]
Incorrect syntax near 'MyTable'

Msg 2714, Level 16, State 6, Line 35
There is already an object named 'MyTable' in the database

Msg 102, Level 15, State 1, Line 156
Incorrect syntax near 'END'

1) post the script 2) post the actual error – Nov 10 '17 at 19:25 — , Nov 10 '17 at 19:25

score 1 · Answer 1 · answered Nov 10 '17 at 21:14

You need to remove the GO that is within the BEGIN and END block. GO is a batch separator which is like telling the block to execute before it's even ended. Check out this previous post for more info.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spDropAndCreate]
AS
BEGIN
   SET NOCOUNT ON;

   IF OBJECT_ID ('dbo.MyTable','U') IS NOT NULL
     DROP TABLE [dbo].[MyTable]

   CREATE TABLE [dbo].[MyTable]
   (
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Col1] [nvarchar](50) NULL,
    [Col2] [nvarchar](50) NULL,
    [Col3] [nvarchar](50) NULL,
    [Col4] [nvarchar](50) NULL,
    [Col5] [nvarchar](50) NULL,
    [Col6] [nvarchar](50) NULL,
    [Col7] [nvarchar](50) NULL,
    [Col8] [nvarchar](50) NULL,
    [Col9] [nvarchar](50) NULL,
    [Col10] [nvarchar](50) NULL
   )    
END

S3S · Accepted Answer · 2018-05-30T12:52:18.057

-1

Add a GO after the DROP TABLE statement to separate the batches.

This won’t work for a procedure though. Instead uses exists when sys objects.

If exists(select 1 from sys.objects where name = 'mytable')
drop mytable

edited May 30 '18 at 12:52

answered Nov 10 '17 at 20:18

S3S

24,809
5
26
45

You can't have `GO` within a Stored Procedure, and everything between `BEGIN` and `END` is the Stored Procedure body – Red May 30 '18 at 11:32
That’s correct I overlooked it was in a procedure. Fixed for a proc @Red Good eye – S3S May 30 '18 at 13:03

How to DROP and CREATE a table in SQL Server 2016 Stored Procedure

2 Answers2