INSERT rows multiple times based on a column value from another table

Question

Mainly, I would like to insert a row in table 1 multiple times, based on an integer value in a column of table 2.

My situation

Table 2 contains a column 'SKU' and 'stock', and I would like to insert the 'SKU' and a timestamp into table 1. I want this row duplicated for 'stock'-value times in table 1.

I currently have the following query:

DECLARE @Count int = 1
WHILE @Count <= ....
BEGIN
    INSERT INTO table1 (table1.SKU, table1.timestamp_in) 
    SELECT table2.SKU, "some timestamp" FROM table2
    SET ...
END

I am not sure if this is the correct approach. I would like to run this loop for 'table2.stock' times.

My question is: Is this possible with just a SQL query, or should it be a better practice to build some (in my case) java code for this?

You can do it in a procedure or increment the variable in your code.If you`ll do it fairly often use a procedure. — Mihai
– Mihai, Commented Jan 26, 2015 at 10:22
@Mihai Thanks for your comment. I am just going to do this once, later on this will be done from java code. How can the increment of the variable be done? With a select before the loop? — bashoogzaad
– bashoogzaad, Commented Jan 26, 2015 at 10:26

fancyPants · Accepted Answer · 2015-01-26 10:55:35Z

3

You don't need a procedure or anything like that. All you need is a table containing just numbers. I'm creating this table on the fly with this in this example:

SELECT aa.a + 10*bb.b + 100*cc.c AS numbers FROM (
SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) aa 
, (SELECT 0 b UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) bb 
, (SELECT 0 c UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) cc;

This creates the numbers 0 till 999.

Now you join your table2 with this numbers table in the range of stock. Your final query looks like this:

INSERT INTO table1 (table1.SKU, table1.timestamp_in) 
SELECT table2.SKU, "some timestamp" FROM table2
INNER JOIN (
    SELECT aa.a + 10*bb.b + 100*cc.c AS n FROM (
    SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) aa 
    , (SELECT 0 b UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) bb 
    , (SELECT 0 c UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) cc
) numbers ON numbers.n BETWEEN 0 AND table2.stock /*assuming you have no negative stock*/

Just make sure, that the numbers table contains more numbers than the highest value in the stock column.

edited Jan 26, 2015 at 10:55

answered Jan 26, 2015 at 10:44

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

bashoogzaad Over a year ago

Thanks for your answer. I assume I need to create the table 'numbers' first. You say you create the table on the fly with that code, which I do not totally understand. When I run the query, it says: 'Unknown column numbers.n'

fancyPants Over a year ago

It's just a typo. numbers is the alias for the subquery. I named the column in the subquery also numbers. Fixed it now by renaming it to n.

bashoogzaad Over a year ago

Okay now I understand, and it seems to work! Only, the row is inserted once too much. So I changed it and now it works (table2.stock-1). Thank you very much!

Marcos Buarque Over a year ago

@francyPants, your example worked perfectly well for me, except for the fact that I probably should use 1 instead of 0 on the ON clause for the JOIN. I have thus changed it to BETWEEN 1 AND table2.stock. This gives me the exact number of items in stock. By using 0, I get one more item on the resulting query.

Collectives™ on Stack Overflow

INSERT rows multiple times based on a column value from another table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related