SQL dynamic query for insert

Question

I have a SQL Server table with these columns:

Product | QTY | DISCREPANCIES

and I want to insert multiple records using a stored procedure.

Inputs for my procedure are :

    declare @Product varchar(50) = 'product1';
    declare @QTY int = 1;
    declare @DISCREPANCIES1 varchar(50) = 'Defected';
    declare @DISCREPANCIES2 varchar(50) = 'Shorted';
    declare @DISCREPANCIES3 varchar(50) = 'DifferentProduct';
    declare @DISCREPANCIES4 varchar(50) = 'Extra';
    declare @DISCREPANCIESQTY1 int = 1;
    declare @DISCREPANCIESQTY2 int = 1;
    declare @DISCREPANCIESQTY3 int = 1;
    declare @DISCREPANCIESQTY3 int = 1;

I want the inserted output like :

product1 |  1  | Defected
product1 |  1  | Shorted
product1 |  1  | DifferentProduct
product1 |  1  | Extra

How can I insert these values using while loop or using anything else?

score 0 · Answer 1 · answered Jul 14 '17 at 15:01

This is literally a basic insert, though I don't understand why you are taking in 4 rows of data versus passing in a table type parameter.

insert into someTable (Product, QTY, DISCREPANCIES)
values
(@Product,@DISCREPANCIESQTY1,@DISCREPANCIES1),
(@Product,@DISCREPANCIESQTY2,@DISCREPANCIES2),
(@Product,@DISCREPANCIESQTY3,@DISCREPANCIES3)

Also, you didn't explain what these parameters are for or how they are related so I don't know if you want to use @QTY or the ones above. Anyways, you get the picture...

SQL dynamic query for insert

1 Answers1