Inserting a multiple records in a table with while

问题:

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 ','.

回答1:

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.

回答2:

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.