How can I insert rows of one table into multiple tables using a SQL Server stored procedure?

Okay, I'm not sure exactly where you are struggling, so here's a script that sort of does what you asked for. None of this is too hard to follow, so maybe have a scan through it, and then let me know which bits are confusing?

Set up the table structure:

CREATE TABLE tempDataTable (
    TempProductId INT,
    TempProductUrl VARCHAR(512),
    TempProductPrice VARCHAR(50),
    TempProductPricePerUnit VARCHAR(50),
    TempProductName VARCHAR(512));
INSERT INTO tempDataTable SELECT 2491, 'https://yadayada1', '£1.65/unit', '46p/100g', 'Yeo Valley Little Yeos, blah';
INSERT INTO tempDataTable SELECT 2492, 'https://yadayada2', '60p/unit', '1p/ea', 'Sainsbury''s Little Ones, etc';

CREATE TABLE Product (
    ProductId INT PRIMARY KEY,
    ProductName VARCHAR(512),
    ProductUrl VARCHAR(512));

CREATE TABLE ProductPricing (
    ProductPricingId INT IDENTITY(1,1) PRIMARY KEY,
    ProductId INT,
    ProductPrice VARCHAR(50),
    ProductPricePerUnit VARCHAR(50),
    ProductPricingDate DATETIME);

ALTER TABLE ProductPricing ADD CONSTRAINT foreignkey$ProductPricing$Product FOREIGN KEY (ProductId) REFERENCES Product (ProductId);

This gives me three tables to play with, one with some temporary data in it, and two that you want to push the data into, with a couple of primary keys, and a foreign key constraint to ensure integrity between the two tables.

Good so far?

Now to split the data between the two tables is as simple as:

INSERT INTO Product (ProductId, ProductName, ProductUrl) SELECT TempProductId, TempProductName, TempProductUrl FROM tempDataTable;
INSERT INTO ProductPricing (ProductId, ProductPrice, ProductPricePerUnit, ProductPricingDate) SELECT TempProductId, TempProductPrice, TempProductPricePerUnit, GETDATE() FROM tempDataTable;

If you run that then you should end up with data in your two tables, like this:

Product

ProductId   ProductName ProductUrl
2491    Yeo Valley Little Yeos, blah    https://yadayada1
2492    Sainsbury's Little Ones, etc    https://yadayada2

ProductPricing

ProductPricingId    ProductId   ProductPrice    ProductPricePerUnit ProductPricingDate
1   2491    £1.65/unit  46p/100g    2020-04-27 14:29:14.657
2   2492    60p/unit    1p/ea   2020-04-27 14:29:14.657

Now there's a whole load of questions that arise from this:

• how are you going to cope with running this more than once, because the second time you run it there will be primary key violations?
• do you want to clear down the temporary data somehow on successful completion?
• do you want to use the system date as the pricing date, or are there more columns off the edge of your image?
• do you want to check the data for duplicates and deal with them before running the script, or it will just fail?
• if you do get a duplicate then do you skip it, or update the data (MERGE)?
• why do you want this as a stored procedure? I mean it's easy enough to make into one, but I don't see why this would need to be repeatable... without seeing the other "moving parts" in this system anyway.

I'm guessing that you are loading bulk data into that temporary table somehow, from an Excel workbook, or XML, or similar. So all you want is a way to "tear the data up" into multiple tables. If this is indeed the case, then using a tool like SSIS might be more practical?

Okay, so that's 90% there, but you need two other things:

• cope with situations where the product id already exists - don't try to insert it a second time as it will fail;
• where the product id already exists then update the price data.

This should handle the first tweak:

INSERT INTO Product (ProductId, ProductName, ProductUrl) SELECT t.TempProductId, t.TempProductName, t.TempProductUrl FROM tempDataTable t
WHERE NOT EXISTS (SELECT * FROM Product p WHERE p.ProductId = t.TempProductId);

...and to UPDATE prices where the data already exists, or INSERT them if they don't exist, well you can use a MERGE statement:

MERGE
    ProductPricing AS [target]
USING (SELECT TempProductId, TempProductPrice, TempProductPricePerUnit, GETDATE() AS ProductPricingDate FROM tempDataTable)
    AS [source] (
        ProductId,
        ProductPrice,
        ProductPricePerUnit,
        ProductPricingDate)
    ON ([target].ProductId = [source].ProductId)
WHEN MATCHED THEN
    UPDATE SET
        ProductPrice = [source].ProductPrice,
        ProductPricePerUnit = [source].ProductPricePerUnit,
        ProductPricingDate = [source].ProductPricingDate
WHEN NOT MATCHED THEN
    INSERT (
        ProductId, 
        ProductPrice, 
        ProductPricePerUnit, 
        ProductPricingDate)
    VALUES (
        [source].ProductId,
        [source].ProductPrice,
        [source].ProductPricePerUnit,
        [source].ProductPricingDate);

Actually, re-reading your comment, I don't think you even need a MERGE (but I'm going to leave it there anyway, as it took me a little effort to write it).

I think your second case is as simple as just letting the second INSERT always run. There's two scenarios:

• if there's already an entry for that product - then just add a new row to the ProductPricing table, so you will have one product, and two (or more) prices, each with a different date;
• if it's a new product - then add the product and the price, so you will have one product and one price (until a new price arrives).

...and I can't resist adding, this is because you are using a natural key, i.e. a key from your data, so it doesn't change as you load it. If you were using a surrogate key (e.g. an IDENTITY that you got when you inserted the Product) then this wouldn't work, you would need to go and look up the surrogate key, then use this so your foreign key constraint worked properly. It's probably best to not think about this too hard?