SQLTeam.com | Weblogs | Forums

Run insert command a specified number of times

Hello,
I'm struggling to find a way to accomplish this, I can find many examples of how to do this with a loop, or perhaps even in php but the way we have this statement set up I can't get anything to work.

I will specify the amount of duplicates I want inserted in to the table, in this case 5... how do I run this that many times without a stored procedure? I hope this is the right place to seek some help.

$duplicates=5;
 $sql="INSERT INTO Plate (Thickness, Grade, Length, Width, DimC, DimD, Tag, Win, Cost, Heat, Crop, PoNum, NestNum, Origin, Location, MachTime, PMachine, SetupTime, Facility, POExRate)";
    $sql=$sql . " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
    $stmt = $db->prepare_query($sql, 
    array( $Thickness, $Grade, $Length, $Width, $DimC, $DimD, $Tag, $Win, $Cost, $Heat, $Crop, $PoNum,  $NestNum, $Origin, $Location,  $MachTime, $PMachine, $SetupTime, $Facility, $POExRate));

What values do you want inserted? And where are you going to get those values?

Generally, we don't insert duplicate rows into a table - there is no reason to have duplicates, which is the entire purpose of using an RDBMS. Why do you need to be able to create these duplicate rows - and are they really duplicates?

To generate a set number of rows - with the 'same' values:

 Select *
   From (Values (0), (0), (0), (0), (0)) As t(n)
  Cross Join (Values ('col1', 'col2', 'col3')) As d(col1, col2, col3)

To make it dynamic - you would need either a table of numbers (tally table), or generate the number of rows needed. You can generate up to 100 rows with this construct:

Declare @duplicates int = 5;

   With t(n)
     As (
 Select t.n
   From (Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
        )
      , iTally (n)
     As (
 Select Top (@duplicates)
        checksum(row_number() over(Order By @@spid))
   From t t1, t t2
     )        
 Select *
   From iTally                  it
  Cross Join (Values ('col1', 'col2', 'col3', 'col4')) As d(col1, col2, col3, col4)
1 Like

From within SSMS:

GO
INSERT INTO Plate (Thickness, Grade, Length, Width, DimC, DimD, Tag, Win, Cost, Heat, Crop, PoNum, NestNum, Origin, Location, MachTime, PMachine, SetupTime, Facility, POExRate)
VALUES('...',,...?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
/* simply change 5 to the number of times you want to run the INSERT statement */
GO 5

Thank you everyone for your suggestions, I've got this working now, this was very helpful.