sql-servert-sqlstored-proceduressql-insertbulkinsert

SQL Server : stored procedure to insert more than one row


I have a table where you can request a number of devices.

Request table:

ID     noOfdevice    requestedby
--------------------------------
 1         4            sam     

How can I create stored procedure or for the device table to insert 5 new rows for the devices as per noOfdevice column from the Request table?

Device table:

ID        requestedby
---------------------
dev1           sam
dev2           sam
dev3           sam
dev4           sam

Solution

  • You can use a loop to run multiple insert statements. For MySQL: https://dev.mysql.com/doc/refman/8.0/en/while.html

    Here is an very short Pseudocode-Example:

    counter INT =1 ;
    
    SELECT noOfdevice
    INTO var1
    FROM Request table
    WHERE requestedby= [NAME];
    
      WHILE counter <= var1 DO
           insert....
      END WHILE;
    

    The exact Syntax may vary from different databases