SQL Server INSERT in Loop

Question

I have this SQL Script:

DECLARE @Loop INT
SET @Loop = 1
DECLARE @Result table([1] int, [2] int,[3] int,[4] int,[5]);

WHILE (@Loop <=5)
BEGIN

INSERT INTO @Result(@Loop)
 SELECT Id FROM Students WHERE Id=@Loop

SET @Loop= @Loop+ 1
END

I got an error in this line:

INSERT INTO @Result(@Loop)

Is it possible to use this way to insert data into column names using loop ? I mean dynamicaly

Thanks

By looking at you code, it seems like you want to convert your rows in columns correct? — AK47, Sep 17 '14 at 08:42
If yes then you can use PIVOT. But if you want to follow same method then instead of writing INSERT statement directly, try dynamic statement.Means build a string of statement & then execute it. — AK47, Sep 17 '14 at 08:44
http://stackoverflow.com/a/12896225/3130094 - use dynamic sql — Jamie Dunstan, Sep 17 '14 at 08:44
After writing some code, I found that inserting values multiple times will insert 5 rows in you table. I think PIVOT is best option for you. — AK47, Sep 17 '14 at 08:52

Jamie Dunstan · Accepted Answer · 2014-09-17T09:53:22.643

This is as close as I can get to what you have.

I'm using dynamic sql to build the insert sql.

I'm using a temporary table #Result instead of a variable @Result because the scope of the @Result variable would mean that this technique would not work.

DECLARE @Loop INT;
SET @Loop = 1;

CREATE TABLE #Result([1] int, [2] int, [3] int, [4] int, [5] int);

DECLARE @Sql AS NVARCHAR(MAX);
DECLARE @LoopNVARCHAR AS NVARCHAR(10);

WHILE (@Loop <= 5)
BEGIN
    SET @LoopNVARCHAR = CAST(@Loop AS NVARCHAR(10));
    SET @Sql = N'INSERT INTO #Result([' + @LoopNVARCHAR + ']) SELECT Id FROM Students WHERE Id = ' + @LoopNVARCHAR;
    exec (@Sql)
    SET @Loop = @Loop + 1
END

SELECT * FROM #Result
DROP TABLE #Result

I agree with the above it work for the scenario at hand, I also suggest trying to minimize the use of while loops unnecessarily. This might cause a performance overhead when working with too much data. The cursor would have done the trick. — Lord-David, Sep 17 '14 at 09:34

SQL Server INSERT in Loop

1 Answers1