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

Question

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

question from:https://stackoverflow.com/questions/65932295/problem-with-create-and-drop-temp-table-several-times-in-a-query

Answer 1

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.