Combining 2 insert into in one command

Question

I couldn't figure this out: I want to add a row in a table-a and 3 columns of this row will come from table-b and other 2 columns will come from e.g textboxes... This code didn't work...

SqlCommand cmd35 = new SqlCommand("INSERT INTO BTmr (Barcode,[Machine Name],[Machine ID]) SELECT Barcode,[Machine Name],[Machine ID] FROM BkmP WHERE barcode like '" + c13 + "%' UNION INSERT INTO BTmr([Repair Cost],[Repair Date],Barcode)values (@cst,@rprd)", connection);

            cmd35.Parameters.AddWithValue("@cst", textBox10.Text);
            cmd35.Parameters.AddWithValue("@rprd", dateTimePicker1.Text);

select the columns from table-b before putting in table-a and you will have all the values in to be input for table-a. — randominstanceOfLivingThing, Apr 28 '16 at 22:39
Your select should be executed outside the insert statement so that you read the column values into variables. — randominstanceOfLivingThing, Apr 28 '16 at 22:40

score 0 · Accepted Answer · edited May 23 '17 at 12:31

Just put the parameters as static values in the select statement.

SqlCommand cmd35 = new SqlCommand("INSERT INTO BTmr (Barcode,[Machine Name],
    [Machine ID],[Repair Cost],[Repair Date]) SELECT Barcode,[Machine Name],
    [Machine ID],@cst AS [Repair Cost],@rprd AS [Repair Date] FROM BkmP WHERE
    barcode like '" + c13 + "%', connection);

cmd35.Parameters.AddWithValue("@cst", textBox10.Text);
cmd35.Parameters.AddWithValue("@rprd", dateTimePicker1.Text);

While your're at it, parameterize that where clause: https://stackoverflow.com/a/251380/123422

Combining 2 insert into in one command

1 Answers1