SQL: Insert Query Result into Temp Table inside While Loop

Question

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

Which dbms are you using? (The above code is product specific.) — jarlh
– jarlh, Commented Jan 4, 2023 at 12:37

Mohammad Azizkhani · Accepted Answer · 2023-01-04 12:42:47Z

1

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

edited Jan 4, 2023 at 12:42

answered Jan 4, 2023 at 12:30

Mohammad Azizkhani

3424 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lim Over a year ago

I tried creating temp table. But while executing the procedure. Its just loading. Does not return result.

Collectives™ on Stack Overflow

SQL: Insert Query Result into Temp Table inside While Loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related