'Insert query for multiple row where two columns data always remain same

I have a query to insert multiple rows in which two-column data remains the same in each row. Is there any way in which we can write an optimized query rather than placing an insert statement again.

INSERT INTO public.shops(id, shop_id, tier_id)
 VALUES
(uuid_generate_v1(),'8011',(SELECT ID FROM PUBLIC.BETTING_TIER WHERE NAME='Daily Million 6 - North Ire')),

(uuid_generate_v1(),'8012',(SELECT ID FROM PUBLIC.BETTING_TIER WHERE NAME='Daily Million 6 - North Ire')),

(uuid_generate_v1(),'8013',(SELECT ID FROM PUBLIC.BETTING_TIER WHERE NAME='Daily Million 6 - North Ire')),

(uuid_generate_v1(),'8014',(SELECT ID FROM PUBLIC.BETTING_TIER WHERE NAME='Daily Million 6 - North Ire')),

(uuid_generate_v1(),'8015',(SELECT ID FROM PUBLIC.BETTING_TIER WHERE NAME='Daily Million 6 - North Ire')),
sqlpostgresqlsql-insert


Solution 1:[1]

Rephrase as a pure INSERT INTO ... SELECT:

INSERT INTO public.shops (id, shop_id, tier_id)
SELECT uuid_generate_v1(), t2.shop_id, t1.ID
FROM PUBLIC.BETTING_TIER t1
CROSS JOIN
(SELECT '8011' AS shop_id UNION ALL SELECT '8012' UNION ALL SELECT '8013'
 UNION ALL '8014' UNION ALL '8015') t2
WHERE t1.NAME = 'Daily Million 6 - North Ire';

Solution 2:[2]

An alternative to Tim's solution would be to use generate_series to create the shop ids, in case they're sequential:

INSERT INTO public.shops(id, shop_id, tier_id)
SELECT uuid_generate_v1(), i::text, b.id 
FROM generate_series(8011,8015,1) i
CROSS JOIN betting_tier b
WHERE b.name = 'Daily Million 6 - North Ire';

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen
Solution 2 Jim Jones

Related Questions