sql multiple insert on one table, while looping/iterating over another table?

Question

Hi Guys,

I have two table "TempStore" and "Store" with the same column called "Items".

There is data in "TempStore" table which I need to move over to "Store" table which requires few modifications.

I need to iterate over "TempStore" data (i.e. items) and insert into Store...

More specifically: How can I iterate over TempStore (in sql) where "for each item in 'TempStore' I need to store 2 or maybe 3 items in 'Store' with little modification", how can I accomplish this?

What I want to do is take each rowdata from "[SELECT * FROM TempStore]" and insert three records in "Store" with being able to change "items"

Hints, Tips, answers welcome.....

Thanks,

Voodoo

Answer 1

try INSERT-SELECT:

INSERT INTO Store
        (col1, col2, col3...)
    SELECT
        col1, col2, col3...
        FROM TempStore
        WHERE ...

just make the SELECT return one row for every insert, and produce the values in the Cols that you need. You might need CASE and a join to another table to make the extra rows.

EDIT based on comments, OP wanted to see the numbers table in action

Lets say TempStore table has {Items, Cost, Price, ActualCost, ActualPrice} But in the Store table I need to store {Items, Cost, Price}. The ActualCost and ActualPrice from TempStore datarow would need to be added as another row in Store....(I hope this makes sense)....Anyways, is the solution using "WHILE-BEGIN-END"??

CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY)
INSERT INTO Numbers VALUES(1)
INSERT INTO Numbers VALUES(2)
INSERT INTO Numbers VALUES(3)


INSERT INTO Store
        (Items, Cost, Price)
    SELECT
        t.Items, t.Cost
            ,CASE
                 WHEN n.Number=1 THEN t.Price
                 WHEN n.Number=2 THEN t.ActualCost
                 ELSE t.ActualPrice
             END
        FROM TempStore         t
            INNER JOIN Numbers N ON n.Number<=3
        WHERE ...

you could even use a UNION:

INSERT INTO Store
        (Items, Cost, Price)
    SELECT
        t.Items, t.Cost, t.Price
        FROM TempStore t
    UNION ALL
    SELECT
        t.Items, t.Cost, t.ActualCost
        FROM TempStore t
    UNION ALL
    SELECT
        t.Items, t.Cost, t.ActualPrice
        FROM TempStore t

either the Numbers table or the UNION will we WAY better than a loop!

Answer 2

INSERT INTO Store ( SELECT * FROM TempStore UNION ALL SELECT * FROM TempStore )

The above statement will insert two rows in the store for each row in the TempStore. You can change the SELECT * to whatever modification that you want to do to the item.

Answer 3

OK, I think KM has proposed an excellent solution involving a "numbers table". However, VoodooChild has requested in a comment that I post example code for my suggestion of using WHILE-BEGIN-END around an INSERT-SELECT.

I have created two tables like VoodooChild's Store and TempStore.

Store has columns StoreID, StoreName, StoreState, StoreNumber.

TempStore has columns TempStoreID, TempStoreName.

I prepopulated TempStoreName with the values First, Second, Third and Fourth.

Now, my SQL will insert three records into the Store table for every record in the TempStore table that meets the condition in the WHERE clause. That condition is the length of the TempStoreName, obviously not a real-world example.

DECLARE @counter int 
SET @counter = 0;
WHILE @counter < 3
BEGIN
INSERT INTO Store (StoreName, StoreState, StoreNumber)
    SELECT TempStoreName, 'AZ', @counter FROM TempStore WHERE LEN(TempStoreName) = 5
SET @counter = @counter + 1
END

The result of this when applied to an empty Store table is:

StoreID StoreName   StoreState  StoreNumber
1           First       AZ          0
2           First       AZ          1
3           First       AZ          2
4           Third       AZ          0
5           Third       AZ          1
6           Third       AZ          2

So, this approach works. It appears to meet VoodooChild's needs. It may or may not be the very best choice, but there are other factors involved in the decision that we don't know, such as how many times this operation is going to be repeated.

Answer 4

Given your latest comment, this should give you what you need. You should probably have some way of differentiating the values in your Stores table once they get there. Perhaps an "actual" BIT column or something similar:

INSERT INTO Stores (item, cost, price, actual)
SELECT item, cost, price, 0
FROM TempStores
UNION ALL
SELECT item, actual_cost, actual_price, 1
FROM TempStores

If you needed to adjust the columns (for example, increase actual_price by 10%) then you could do this:

INSERT INTO Stores (item, cost, price, actual)
SELECT item, cost, price, 0
FROM TempStores
UNION ALL
SELECT item, actual_cost, 1.1 * actual_price, 1
FROM TempStores
WHERE actual_cost IS NOT NULL

I also added a WHERE clause to the second SELECT statement to show that you can filter the rows. That WHERE clause will only affect the second SELECT. So, you could also do this:

INSERT INTO Stores (item, cost, price, actual)
SELECT item, cost, price, 0
FROM TempStores
WHERE cost IS NOT NULL
UNION ALL
SELECT item, actual_cost, 1.1 * actual_price, 1
FROM TempStores
WHERE actual_cost IS NOT NULL