Inserting a multiple records in a table with while loop [closed]

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
– Kermit, Commented Aug 28, 2012 at 16:18
adding in now ... added something incorrect .. correcting it — simranNarula
– simranNarula, Commented Aug 28, 2012 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
– HABO, Commented Aug 28, 2012 at 16:25
Does Line 17 mean anything with respect to the abridged code you posted? — HABO
– HABO, Commented Aug 28, 2012 at 16:28

Klaus Byskov Pedersen · Accepted Answer · 2012-08-28 16:25:49Z

14

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, 2012 at 16:25

Klaus Byskov Pedersen

122k31 gold badges192 silver badges223 bronze badges

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

6 Comments

simranNarula Over a year ago

yeah but I want to loop through .. there are lots of these records

D. Lambert Over a year ago

You're working in a set-based environment -- avoid loops if possible.

Lamak Over a year ago

@user1630846 - a couple hundred of rows it's not lots of records, by far.

Klaus Byskov Pedersen Over a year ago

@user1630846 looping is slow and should be avoided whenever possible when working with relational databases.

swasheck Over a year ago

@user1630846 See: RBAR

|

Piotr Sobiegraj · Accepted Answer · 2012-08-28 16:55:56Z

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.

Collectives™ on Stack Overflow

Inserting a multiple records in a table with while loop [closed]

2 Answers 2

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Linked

Related