Insert multiple records in SQL where values are all combinations of defined ranges in single query

Question

How to write sql query

I need something like

Insert Into database.table (userID,credID,time) 
values 
userId = for all in (10,15,12,17,14,267,16,689,18,7659,20)
credID = for all in (1,2,3,4,5) 
time = constant (forall the same)

now database structure

userID,credID,time 10,34,2013-12-12 10,54,2013-12-12

so i must get

userID,credID,time
10,34,2013-12-12
10,54,2013-12-12
10,1,2013-12-12
10,2,2013-12-12
10,3,2013-12-12
10,4,2013-12-12
10,5,2013-12-12
11,1,2013-12-12
11,2,2013-12-12
11,3,2013-12-12
11,4,2013-12-12
11,5,2013-12-12
....

for logical algoritm must be like

for each userID in range (10,11,12,13,14,15,16,17,18,19,20) insert new fields with credID values (1,2,3,4,5)

For single userID i can create single credID with query insert into database.table (userID,credID,time) values (10,1,2013-12-12) but i need to insert multiple credID for multiple userID

your sql pseudocode was little confusing, I edited it - is this what you mean? — Tomas
– Tomas, Commented Dec 30, 2013 at 14:15
maybe. credID is new parametr not in table. userId always is in table with others credID — user3146584
– user3146584, Commented Dec 30, 2013 at 14:20
I don't understand what you mean by "userId always is in table with others credID" — Tomas
– Tomas, Commented Dec 30, 2013 at 14:25
userId constantly present in the database with other credID, i need insert new credID for existing userID — user3146584
– user3146584, Commented Dec 30, 2013 at 14:28
1) for all of them or only for some? 2) these userIDs are all part of database.table? — Tomas
– Tomas, Commented Dec 30, 2013 at 14:29

krokodilko · Accepted Answer · 2013-12-30 15:16:05Z

2

Try this way:

INSERT INTO table1( userID,credID,time)
SELECT x,y,'2013-12-12' 
FROM (
  SELECT 1 As x union
  SELECT 2 union
  SELECT 3 union
  SELECT 4 union
  SELECT 5
) xx
CROSS JOIN (
  SELECT 10 As y union
  SELECT 11 union
  SELECT 12 union
  SELECT 13 union
  SELECT 15 union
  SELECT 16 union
  SELECT 17 union
  SELECT 18 union
  SELECT 19 union
  SELECT 20
) yy

Demo: http://www.sqlfiddle.com/#!2/8398d/1

EDIT.

If these two lists of numbers are dense, there is another trick with table of numbers:

CREATE TABLE numbers( x int primary key auto_increment );

INSERT INTO numbers
SELECT null FROM information_schema.columns
LIMIT 100;

CREATE TABLE   table2
  (userID int,credID int,time date);

INSERT INTO table2( userID,credID,time)
SELECT n1.x,n2.x,'2013-12-12' 
FROM numbers n1
CROSS JOIN numbers n2
WHERE n1.x BETWEEN 1 AND 5
  AND n2.x BETWEEN 10 AND 20
;

demo: http://www.sqlfiddle.com/#!9/e121d/1

EDIT.

There is an another trick with the table of numbers.
If you want to pass these two lists as comma separated strings, then try this query:

CREATE TABLE numbers( x int primary key auto_increment );

INSERT INTO numbers
SELECT null FROM information_schema.columns
LIMIT 100;

CREATE TABLE   table1
  (userID int,credID int,time date);

INSERT INTO table1( userID,credID,time)
SELECT xx,yy,'2013-12-12' 
FROM (
        SELECT reverse( if( locate(',',reverse(SUBSTRING_INDEX( y, ',', x ))) > 0,
                          substr( reverse(SUBSTRING_INDEX( y, ',', x )), 1, locate(',',reverse(SUBSTRING_INDEX( y, ',', x ))) -1 ),
                          reverse(SUBSTRING_INDEX( y, ',', x ))
                     )) AS xx
        FROM (  select '1,22,333,44,51,656'  y ) q
        JOIN numbers n
        ON n.x <= length( y ) - length( replace( y, ',','')) + 1
) q1
CROSS JOIN
(
        SELECT reverse( if( locate(',',reverse(SUBSTRING_INDEX( y, ',', x ))) > 0,
                          substr( reverse(SUBSTRING_INDEX( y, ',', x )), 1, locate(',',reverse(SUBSTRING_INDEX( y, ',', x ))) -1 ),
                          reverse(SUBSTRING_INDEX( y, ',', x ))
                     )) AS yy
        FROM (  select '111,222,3333,444,54,656'  y ) q
        JOIN numbers n
        ON n.x <= length( y ) - length( replace( y, ',','')) + 1
) q2
;

Demo --> http://www.sqlfiddle.com/#!9/83c86/1

edited Dec 30, 2013 at 15:16

answered Dec 30, 2013 at 14:13

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

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

10 Comments

Tomas Over a year ago

wow, nice extension for my solution :-) Nice trick. You need however another language to generate the code dynamically

krokodilko Over a year ago

@Tomas Yes, you are absolutely right, these two list must be created somehow, either dynamicaly or someone must enter numbers manually.

Tomas Over a year ago

Maybe you could do it somehow in the procedural part of SQL itself (which is used for programming procedures etc) :-) But that would probably not be the standard SQL

Tomas Over a year ago

+1 for the first approach :-) I think its probably necesary for the OP solution. The second one is a nice trick too but too complicated already

krokodilko Over a year ago

@Tomas, if you don't belive, please take a look at simple demo for PostgreSQL: sqlfiddle.com/#!12/d0ff6/2

|

Tomas · Accepted Answer · 2013-12-30 14:41:52Z

0

This is not possible in SQL in general, unless those value ranges are part of some other table. In that case, you can do it by joining the ranges without any joining key - that will produce the desired cartesian product:

insert into database.table (userID,credID,time)
select userID, credID, '2011-12-12'
from (select userID
      from database.table
      where userID in (10,15,12,17,14,267,16,689,18,7659,20))
  join (select credID 
        from database.table
        where credID in (54, 34, 1, 2, 3, 4, 5))

But as I said above - in this code we assume that the database.table contains all listed userIDs and all credIDs 1..5. But that's not necessarily a hinderance - on the contrary, in most practical situations you need to assure that those values exist - not necessarily in the table you insert into, but usually in another table in the database (then you have to modify the subqueries accordingly).

However if for example the credID are new as you said and not contained in any table yet, you have to use approach of @kordiko (credit to him) and combine it in the query above:

insert into database.table (userID,credID,time)
select userID, credID, '2011-12-12'
from (select userID
      from database.table
      where userID in (10,15,12,17,14,267,16,689,18,7659,20)) as t1
  join (select 34 as credID union 
        select 54 union
        select 1 union
        select 2 union
        select 3 union
        select 4 union
        select 5
        ) as t2

edited Dec 30, 2013 at 14:41

answered Dec 30, 2013 at 14:09

Tomas

60.2k54 gold badges251 silver badges386 bronze badges

9 Comments

user3146584 Over a year ago

userID cant be between it must be different 10,52,98,75454,564

Tomas Over a year ago

@user3146584 I don't understand what you mean by "it must be different 10,52,98,75454,564". But you want userIDs that are already part of the database.table? But which one of them? Can you write an SQL query that selects those userIDs from database.table?

user3146584 Over a year ago

select userID from database.table where userID=1

Tomas Over a year ago

@user3146584 you can use condition like userID in (10,52,98,75454,564). See my edit. Is this what you want?

user3146584 Over a year ago

For single userID i can create single credID with query insert into database.table (userID,credID,time) values (10,1,2013-12-12) but i need to insert multiple credID for multiple userID

|

Collectives™ on Stack Overflow

Insert multiple records in SQL where values are all combinations of defined ranges in single query

2 Answers 2

10 Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related