INSERT repeating values in SQL

Question

Trying to find a simple way to insert some repeating values in two columns in my table, something similar to the rep function in R-

for instance, I need to insert two values (chocolate and vanilla, 4 times each) and I need to insert 4 types of values that repeat twice such as --

flavor_type schedule_type
chocolate   weekly
chocolate   monthly
chocolate   quarterly
chocolate   yearly
vanilla     weekly
vanilla     monthly
vanilla     quarterly
vanilla     yearly

Kirill Polishchuk · Accepted Answer · 2019-07-07 23:40:10Z

4

You can use cross join:

select *
from (values('chocolate'), ('vanilla')) flavor(flavor_type)
cross join (values('weekly'), ('monthly'), ('quarterly'), ('yearly')) schedule(schedule_type)

Output:

flavor_type schedule_type
----------- -------------
chocolate   weekly
chocolate   monthly
chocolate   quarterly
chocolate   yearly
vanilla     weekly
vanilla     monthly
vanilla     quarterly
vanilla     yearly

edited Jul 7, 2019 at 23:40

answered Jul 7, 2019 at 23:26

Kirill Polishchuk

56.4k11 gold badges125 silver badges127 bronze badges

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

2 Comments

S31 Over a year ago

Hm, this seems to give me a table with a total of 5 columns and 2 rows: * one column for flavor type with no repetition - just two distinct values * four columns for each respective schedule type, repeated for flavor

Kirill Polishchuk Over a year ago

@S31, this query produces a resultset with two columns and eight rows, pls see answer.

Collectives™ on Stack Overflow

INSERT repeating values in SQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related