Inserting a multiple records in a table with while loop

Question

I want to insert couple of hundred rows into a table that points to pk in other table. I have been trying to use while loops for inserting multiple records in the table. I am actually setting up my test data.

This is what I am doing :

declare @count int;
set @count = 4018;

while @count <= 5040 
begin
    INSERT INTO [MY_TABLE]
               ([pk_from_other_table]
               ,[..]
               ,[...]
               ,[..]
               ,[..]
               ,[...]
               ,[...]
               ,[..])
        select
               (pk_from_other_table,
               ,[..]
               ,[...]
               ,[..]
               ,[..]
               ,[...]
               ,[...]
               ,[..])
    @count = @count + 1;
end

but this does not seems to work ! can anyone help please... all I want to do is insert number of records = number of records that exist in primary table.

? any idea on how can I achieve this ?

I either get incorrect sytax near count

or

Msg 102, Level 15, State 1, Line 17 Incorrect syntax near ','.

Please update your question, we can't see what you have tried. — Kermit, Aug 28 '12 at 16:18
adding in now ... added something incorrect .. correcting it — simranNarula, Aug 28 '12 at 16:19
You've left out rather a lot, including the schema for `MY_TABLE`. Is there an error message, e.g. indicating that you cannot specify a value for an identity column? — HABO, Aug 28 '12 at 16:25
Does `Line 17` mean anything with respect to the abridged code you posted? — HABO, Aug 28 '12 at 16:28

score 13 · Accepted Answer · answered Aug 28 '12 at 16:25

13

Your current syntax problem is with the @count = @count + 1; which needs to be set @count = @count + 1.

But...

There is no need for a loop. You can simply do one big insert directly, like:

insert into your_table (fk_col, other_col1, other_col2)
select pk_col, 'something', 'something else' 
from your_other_table

If you need to, you can add a where clause to the above.

answered Aug 28 '12 at 16:25

Klaus Byskov Pedersen

117,245
29
183
222

yeah but I want to loop through .. there are lots of these records – simranNarula Aug 28 '12 at 16:26
8

You're working in a set-based environment -- avoid loops if possible. – D. Lambert Aug 28 '12 at 16:28
3

@user1630846 - a couple hundred of rows it's not lots of records, by far. – Lamak Aug 28 '12 at 16:29
2

@user1630846 looping is slow and should be avoided whenever possible when working with relational databases. – Klaus Byskov Pedersen Aug 28 '12 at 16:31
@user1630846 - A recursive common table expression (CTE) may be able to generate the desired data without a `WHILE` loop. – HABO Aug 28 '12 at 16:32
3

@user1630846 See: [RBAR](https://www.google.com/webhp?sourceid=chrome-instant&ie=UTF-8&ion=1#hl=en&sclient=psy-ab&q=rbar+row+agonizing+row&oq=RBAR+Row+&gs_l=hp.3.1.0i30l2.235416.709443.0.711007.30.24.4.1.1.1.312.3682.5j14j4j1.24.0.les%3B..0.0...1c.d1S7a2ITybE&pbx=1&bav=on.2,or.r_gc.r_pw.r_cp.r_qf.&fp=fe97cc68227e2ceb&ion=1&biw=1489&bih=897) – swasheck Aug 28 '12 at 16:40

score 7 · Answer 2 · answered Aug 28 '12 at 16:55

About Msg 102, Level 15, State 1, Line 17 Incorrect syntax near ','.:

you've got double commas in second select list:

select
(pk_from_other_table,
,[..]

remove one.

About inserts: if you are want to insert all records from source table to destination many times you can do it in loop:

declare @count int;
set @count = 4018;

while @count <= 5040 
begin
    INSERT INTO DestinationTableName
               (DestinationTableColumn1Name
               ,DestinationTableColumn2Name --ect
        )
        select
               SourceTableColumn1Name
               ,SourceTableColumn2Name --ect
               from SourceTableName
    set @count = @count + 1;
end

but when you want insert many rows from source table to destination once, where is enough:

INSERT INTO DestinationTableName
            (DestinationTableColumn1Name
            ,DestinationTableColumn2Name --ect
            )
            select
            SourceTableColumn1Name
           ,SourceTableColumn2Name --ect
            from SourceTableName
            where SourceTablePK between 4018 and 5040 --LowerBound and UpperBound
            --or SourceTablePK in (1, 2, 3) etc

You don't have to do it row by row.

Inserting a multiple records in a table with while loop

2 Answers2

Linked