Sql multiple insertion on one table, while loop / iteration on another table?

Question

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

There is data in the TempStore table that I need to move to the Save table, which requires several changes.

I need to iterate over the "TempStore" data (ie the elements) and paste into the Store ...

More specifically: how can I iterate over TempStore (in sql), where "for each item in" TempStore "I need to save 2 or maybe 3 items in the" Store "with a few changes," how can I do this?

What I want to do is take each line from "[SELECT * FROM TempStore]" and insert three entries in "Save" with the ability to change the "elements"

Answer 1

try INSERT-SELECT:

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

just do SELECT return one row for each insert and create the values ​​in Cols that you need. You may need to CASEjoin another table to make additional rows.

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

, TempStore {Items, , , ActualCost, ActualPrice} Store {t, , }. ActualPrice TempStore datarow .... ( , ).... "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 ...

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

Numbers, UNION WAY , !

Answer 2

, , KM , . , VoodooChild , , WHILE-BEGIN-END INSERT-SELECT.

, VoodooChild Store TempStore.

StoreID, StoreName, StoreState, StoreNumber.

TempStore TempStoreID, TempStoreName.

TempStoreName First, Second, Third Fourth.

SQL Store TempStore, WHERE. TempStoreName, , , .

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

Store:

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

, . , , VoodooChild. , , , , , .

Answer 3

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

TempStore. SELECT * , .

Answer 4

, , . , - , . , "" BIT - :

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

(, 10%), :

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

WHERE SELECT, , . WHERE SELECT. , :

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