CODEWITHSUNDEEP
sqlsql-server
Lim
Lim

Reputation: 131

SQL: Insert Query Result into Temp Table inside While Loop

I am trying to insert select query result into a temporary table inside the while loop. But below query gives me this error:

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

DECLARE @V_START INT = 1;
DROP TABLE #TT

WHILE(@V_START < 4)
BEGIN
    SELECT * INTO #TT
    FROM Table
    WHERE Column = @V_START;

    SET @V_START = @V_START + 10;
END

SELECT * FROM #TT

Upvotes: 0

Views: 60

Answers (1)

Mohammad Azizkhani
Mohammad Azizkhani

Reputation: 342

every time your loop is executed a temporary table will create, but first-time execution the temp table does not exist command run successfully and you give the error in the second round of loop execution. You can try it like below :

DECLARE @V_START INT = 1;
DROP TABLE IF EXISTS #TT;
CREATE TABLE #TT 
(
    --your columns
)

WHILE(@V_START < 40)
BEGIN
    INSERT INTO #TT
    (
        --your columns
    )
    SELECT --your columns
    FROM Table
    WHERE Column = @V_START;

    SET @V_START = @V_START + 10;
END

SELECT * FROM #TT

but better solution is using select into with condition for inserting data to temp table:

DROP TABLE IF EXISTS #TT

 SELECT * INTO #TT
    FROM Table
    WHERE Column % 10 = 0 AND Id < 10

    SELECT * FROM #TT

Upvotes: 1

Related Questions