SQL Server 2012 – INSERT INTO from Two Unrelated Tables

Assuming that you are preserving the id assocaited with each name and lastname, run this:

INSERT INTO tbl_2 (id,name) SELECT id,name FROM tbl_1;
INSERT INTO tbl_3 (id,lastname) SELECT id,lastname FROM tbl_1;

If tabless tbl_2 and tbl_3 already have data and you wish to overwrite it, run this:

REPLACE INTO tbl_2 (id,name) SELECT id,name FROM tbl_1;
REPLACE INTO tbl_3 (id,lastname) SELECT id,lastname FROM tbl_1;

If you are not preserving the ID, then run this:

INSERT INTO tbl_2 (name) SELECT name FROM tbl_1;
INSERT INTO tbl_3 (lastname) SELECT lastname FROM tbl_1;

After your query runs, you'll have a set of copied rows (with configid=41) and an identical set of pasted rows (except for the configid=76 and the auto-created id).

Since, these ids are not known in advance, you'll need another way to identify rows of the config table, e.g. a unique key (besides the auto-incrementing one), so you can match (join) the newly created rows with the old ones.

If, for example, the (configid, optionname) is unique, then the following would work:

INSERT INTO pricing
    ( relid, price, ... )          --- relid and all the other columns, 
                                   --- except any autoincrement you may have
SELECT pasted.id, p.price, ....    --- and the same columns here
FROM 
    pricing AS p
  JOIN
    tblproductconfigoptionssub AS copied
      ON  copied.id = p.relid
      AND copied.configid = 41 
  JOIN
    tblproductconfigoptionssub AS pasted
      ON  pasted.optioname = copied.optioname
      AND pasted.configid = 76 ;