CODEWITHSUNDEEP
sqlsql-server
Abolfazl Davoodi Shandiz
Abolfazl Davoodi Shandiz

Reputation: 385

Problem with create and drop temp table several times in a query

I create a temp table in a query and put some value in it. After that I drop that table and create another temp table with same name. but the SSMS shows this error:

There is already an object named '#t' in the database.

This is my code in first scenario:

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

CREATE TABLE #t
(
    b INT,
    c INT
);
INSERT INTO #t
(
    b,
    c
)
VALUES
(   2, -- b - int
    3  -- c - int
    );

SELECT b,
       c
FROM #t;

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

END;


CREATE TABLE #t
(
    b INT,
    c INT
);
INSERT INTO #t
(
    b,
    c
)
VALUES
(   4, -- b - int
    5  -- c - int
    );

SELECT b,
       c
FROM #t;

I put Go phrase after second table dropping and the code runs successfully, insert new values and select them.

This is second scenario:

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

CREATE TABLE #t
(
    b INT,
    c INT
);
INSERT INTO #t
(
    b,
    c
)
VALUES
(   2, -- b - int
    3  -- c - int
    );

SELECT b,
       c
FROM #t;

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

END;

Go -- THIS IS THE DIFFERENCE BETWEEN TWO CODES

CREATE TABLE #t
(
    b INT,
    c INT
);
INSERT INTO #t
(
    b,
    c
)
VALUES
(   4, -- b - int
    5  -- c - int
    );

SELECT b,
       c
FROM #t;

I get confused. Can you help me and explain the usage of GO in queries?

Thanks

Upvotes: 1

Views: 314

Answers (2)

Nicholas Williams
Nicholas Williams

Reputation: 46

The "GO" separates the pieces of code into 2 different batches - so even though they share the same window - they are effectively run in 2 different batches.

GO Utility

The problem you have encountered is a limitation within SQL - you cannot create a temp table with the same name more than once within a batch. Even if they are dropped.

Upvotes: 1

Martin Cairney
Martin Cairney

Reputation: 1767

The scope of the Temp table is the issue here. The table does not get completely dropped until the end of the session or batch - which is where the GO statement comes in. This terminates the batch and allows the Temp table to be fully removed.

Why drop the table and recreate with the same structure? You could just execute DELETE FROM #t to remove the current data and then do the next insert.

Upvotes: 3

Related Questions