CODEWITHSUNDEEP
sqlpostgresql
MangO_O
MangO_O

Reputation: 423

Inserting 2 different rows from a select

Hi I have this query that returns a list of customers that exist in one table, but don't in the other. For each of the returned customer_id, I need to add 2 rows into the profile table.

How would I write in sql the insert for the 2 rows?

select * FROM
(select * 
from customer c 
left outer join profile cp 
on cp.customer_id=c.global_id 
order by c.global_id) as A
where id is NULL;

Thanks!

Upvotes: 0

Views: 52

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657357

A CROSS JOIN (same as , in the FROM list) to a VALUES expression is the shortest and simplest way for a small number of rows:

INSERT INTO profile (<list of columns>)
SELECT <list of columns>
FROM  (
   SELECT <list of columns>
   FROM   customer c 
   LEFT   JOIN profile p ON p.customer_id = c.global_id 
   WHERE  p.customer_id IS NULL
   ) A
, (VALUES (1), (2)) dummy(col_name)
ORDER  BY global_id;

Use an explicit column list in SELECT and INSERT to avoid unexpected results when you change anything later.

You are probably not going to INSERT two identical rows. You could provide useful values in the subquery dummy right away. Possibly with explicit type cast. Like:

(VALUES ('foo'::text), ('bar')) dummy(col_name)

Upvotes: 3

sgeddes
sgeddes

Reputation: 62841

One option would be to create a cartesian product using a cross join:

insert into profile (customerid ...)
select customerid ... 
FROM
   (select * 
   from customer c 
       left outer join profile cp 
           on cp.customer_id=c.global_id 
   ) as A cross join (SELECT 1 col UNION SELECT 2) as B
where id is NULL;

Upvotes: 3

Related Questions