Search code examples
sqlsql-serverssms

Delete Local Temp Tables Within Transaction

I would like to, all within one transaction:

  1. Pull some data into a local temp table
  2. Do some operations on the temp table (for example, alter it then merge it into something -- below I just select)
  3. Delete the temp table
  4. Repeat the process with the same temp table name

The two SQL options are my attempted fix to my original toy example, all return the same errors. I thought adding the BEGIN and END blocks would resolve my issue, but as I said each SQL block returns the same error. I would like to understand what I am missing about deleting local temp tables.

Original Toy

BEGIN TRANSACTION;


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

    SELECT * INTO #my_temp FROM (SELECT 'hello world' AS my_var) tbl;
    SELECT * FROM #my_temp;
    DROP TABLE #my_temp;

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

    SELECT * INTO #my_temp FROM (SELECT 'hello world' AS my_var) tbl;
    SELECT * FROM #my_temp;
    DROP TABLE #my_temp;

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

    SELECT * INTO #my_temp FROM (SELECT 'hello world' AS my_var) tbl;
    SELECT * FROM #my_temp;
    DROP TABLE #my_temp;


COMMIT TRANSACTION;

Returns

1:26:44 PMStarted executing query at Line 1
Msg 2714, Level 16, State 1, Line 14
There is already an object named '#my_temp' in the database.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'tbl'.
Msg 2714, Level 16, State 1, Line 21
There is already an object named '#my_temp' in the database.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near 'tbl'.
Total execution time: 00:00:00.008

Attempt 1

BEGIN TRANSACTION;

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

    SELECT * INTO #my_temp FROM (SELECT 'hello world' AS my_var) tbl;
    SELECT * FROM #my_temp;
    DROP TABLE #my_temp;
END
Begin
    IF OBJECT_ID('tempdb..#my_temp') IS NOT NULL
        DROP TABLE #my_temp;

    SELECT * INTO #my_temp FROM (SELECT 'hello world' AS my_var) tbl;
    SELECT * FROM #my_temp;
    DROP TABLE #my_temp;
END
BEGIN
    IF OBJECT_ID('tempdb..#my_temp') IS NOT NULL
        DROP TABLE #my_temp;

    SELECT * INTO #my_temp FROM (SELECT 'hello world' AS my_var) tbl;
    SELECT * FROM #my_temp;
    DROP TABLE #my_temp;
END

COMMIT TRANSACTION;

Returns

1:19:48 PMStarted executing query at Line 1
Msg 2714, Level 16, State 1, Line 15
There is already an object named '#my_temp' in the database.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'tbl'.
Msg 2714, Level 16, State 1, Line 23
There is already an object named '#my_temp' in the database.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near 'tbl'.
Total execution time: 00:00:00.008

Attempt 2

BEGIN TRANSACTION;

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

    SELECT * INTO #my_temp FROM (SELECT 'hello world' AS my_var) tbl;
    SELECT * FROM #my_temp;
    DROP TABLE #my_temp;

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

    SELECT * INTO #my_temp FROM (SELECT 'hello world' AS my_var) tbl;
    SELECT * FROM #my_temp;
    DROP TABLE #my_temp;

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

    SELECT * INTO #my_temp FROM (SELECT 'hello world' AS my_var) tbl;
    SELECT * FROM #my_temp;
    DROP TABLE #my_temp;
END

COMMIT TRANSACTION;

Returns

1:20:37 PMStarted executing query at Line 1
Msg 2714, Level 16, State 1, Line 14
There is already an object named '#my_temp' in the database.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'tbl'.
Msg 2714, Level 16, State 1, Line 21
There is already an object named '#my_temp' in the database.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near 'tbl'.
Total execution time: 00:00:00.008
Solution

  • You can solve this by adding the GO statement inbetween the blocks. This makes the engine evaluate the blocks as seperate batches, but you can still execute them as one statement. See this post (For MS SQL Server)

    BEGIN TRANSACTION;


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

    SELECT * INTO #my_temp FROM (SELECT 'hello world' AS my_var) tbl;
    SELECT * FROM #my_temp;
    DROP TABLE #my_temp;

GO


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

    SELECT * INTO #my_temp FROM (SELECT 'hello world' AS my_var) tbl;
    SELECT * FROM #my_temp;
    DROP TABLE #my_temp;

GO

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

    SELECT * INTO #my_temp FROM (SELECT 'hello world' AS my_var) tbl;
    SELECT * FROM #my_temp;
    DROP TABLE #my_temp;



COMMIT TRANSACTION;