Temp table in stored procedure is unavailable after first GO

Question

I have 140,000 INSERT statements I need to run. I am trying to insert the record's into a database table, and then input the created PK's into a temp table which I then return. It looks something like this:

CREATE PROCEDURE sp_MassInsertProcedure
    @PassInVariable int
AS
    CREATE TABLE #TempTable(OriginalID int IDENTITY(1,1), NewID int);

    --These two inserts are essentially repeated 70,000 times
    INSERT INTO MyDBTable (something, something, something) VALUES (1, 2, @PassInVariable);
    INSERT INTO #TempTable(NewID) SELECT SCOPE_IDENTITY();

    SELECT * FROM #TempTable;
    DROP TABLE #TempTable;

I have a few other stored procedures just like this that have max 2,000 insert statements and this way works perfectly, but I think because of the number of statements in this particular SP it gives me "Query Finished with Errors" when I try to create the procedure, but doesn't actually create the procedure.

I then added a GO every 200 insert statements, but after the first GO executes the temp table must be released and I get "TempTable is unavailable" error (I also get the same error for the @PassInVariable after the first GO executes). The thing that is strange is that when I place the above explained SQL in a standard script (not in a stored procedure) it works.

So my question is, does anyone know how to persist a temporary table/variable in a single stored procedure that uses multiple batch executions via the GO command?

CREATE PROCEDURE sp_MassInsertProcedure
    @PassInVariable int
AS
    CREATE TABLE #TempTable(OriginalID int IDENTITY(1,1), NewID int);

    --These inserts are essentially repeated 70,000 times
    INSERT INTO MyDBTable (something, something, something) VALUES (1, 2, @PassInVariable);
    INSERT INTO #TempTable(NewID) SELECT SCOPE_IDENTITY();
    GO
    INSERT INTO MyDBTable (something, something, something) (1, 2, @PassInVariable);
    INSERT INTO #TempTable(NewID) SELECT SCOPE_IDENTITY();

    SELECT * FROM #TempTable;
    DROP TABLE #TempTable;

Answer 1

The GO statement in MS SQL releases resources and clean out your session; that is why the temp table is gone as well your variables.

In your stored proc or at least the SQL script above, you don't need the go statement.

The GO statements that you see in other's scripts are to prevent the parser from stop execution after the previous statement errors out. it's similar to Visual Basic "On Error Resume Next" statement. that way your script will continue execution until the end of the script file.

You will see that the GO statement will be utilized mostly in a script file that contain multiple transactions; after each transaction is a go statement. For example a script file that contain multiple CREATE statement for different store procedures. But within one transaction you don't want to use a GO statement because you will loose all of your variables (including temp table(s)) as you see in your script.

I don't see a need in your stored proc though.

Answer 2

Ben, it's a common misconception about "GO" in SQLServer. "GO" is not a part of T-SQL, it's a directive for SQLServer tools (ssms, sqlcmd, sqlwb, isql and others) to split script in different statements. So you can't use it in SPs and functions (even in dynamic SQL), only in scripts.

Although I do not see why your SP couldn't be created, but if you want to run your procedure from Java, .NET, other platforms you need to add

SET NOCOUNT ON

Answer 3

TempTable is unavailable

You're getting this error because a local temporary table is visible only to the connection that creates it and is deleted when that connection is closed. Using GO inside the stored procedure ends the batch and therefore the stored procedure.

A solution is to use a single INSERT query and use the OUTPUT clause to store the newly inserted IDs:

INSERT INTO MyDBTable (something, something, something)
OUTPUT Inserted.ID INTO #TempTable(NewID)
VALUES (1, 2, @PassInVariable);

Replace the ID from INSERTED.ID with the correct ID from MyDBTable

---

To insert a row multiple times, you can use a Tally Table:

WITH E1(N) AS(
    SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b),
Tally(N) AS(
    SELECT TOP(70000)
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM E8
)
INSERT INTO MyDBTable(something, something, something)
OUTPUT Inserted.ID INTO #TempTable(NewID)
SELECT
    1, 2, @PassInVariable
FROM Tally

Replace the TOP with the number of rows you want to insert.

Here is a simplified explanation of Tally Table.

Answer 4

The reason the temporary table is not available is because the stored procedure is:

CREATE PROCEDURE sp_MassInsertProcedure
    @PassInVariable int
AS
    CREATE TABLE #TempTable(OriginalID int IDENTITY(1,1), NewID int);

    --These inserts are essentially repeated 70,000 times
    INSERT INTO MyDBTable (something, something, something) VALUES (1, 2, @PassInVariable);
    INSERT INTO #TempTable(NewID) SELECT SCOPE_IDENTITY();
    GO

The definition stops at the GO, for the reasons explained in other answers.

Now, some useful advice:

• Put the body of the stored procedure in BEGIN/END blocks or BEGIN CATCH/END TRY blocks. Then there is no ambiguity about where it beings or ends.
• You don't need to delete the temporary table at the end of the stored procedure. It happens automatically. I will admit, though, that I personally prefer table variables because it is more obvious that they are deleted.
• The best way to get the returned values is to use the OUTPUT clause. SCOPE_IDENTITY() is pretty safe. But OUTPUT is better and is generally the recommended approach.

Answer 5

Thanks everyone for the answers...I ended up with the following solution:

From the script that was calling my stored procedure I created the #TempTable

CREATE TABLE #TempTable (OriginalID int IDENTITY(1,1), NewID int);
EXEC sp_InsertMassRecords 777;

And then in my stored procedure I had:

CREATE PROCEDURE sp_InsertMassRecords
    @IncomingVariable int
AS
BEGIN
    SET NOCOUNT ON;

    INSERT MyDBTable (col1, col2, col3) 
    OUTPUT Inserted.ID INTO #TempTable(NewID)
    SELECT 1, @IncomingVariable, 3 UNION ALL
    SELECT 4, @IncomingVariable, 6 UNION ALL...

I repeated the INSERT/OUTPUT lines after about every 100 select statements or so and the whole thing ran successfully pretty fast!