title: postgresql insert with multiple selects
body: <p>If this is how I insert one record:</p>
  
  <pre><code>insert into fund_data 
      (fund_entries_id
       ,fund_val
       ,bbg_pulls_id)
  (select fe.id, 1,20
      from
          fund_entries fe
      where
           fe.company_id = 399
          and fe.fiscal_prd_end_date = '2016-09-30')
  </code></pre>
  
  <p>And this is how I insert another record:</p>
  
  <pre><code>insert into fund_data 
      (fund_entries_id
       ,fund_val
       ,bbg_pulls_id)
  (select fe.id, 567,20
      from
          fund_entries fe
      where
           fe.company_id = 5
          and fe.fiscal_prd_end_date = '2016-09-28')
  </code></pre>
  
  <p>How do I insert them both in the same insert statement? Note, that in reality, there will be hundreds of thousands of inserts, not just two, so efficiency is very important.</p>
  
  <p>In other words, from record to record in the insert <code>company_id</code>, <code>fund_val</code>, <code>fiscal_prd_end_date</code> change. <code>bbg_pulls_id</code>stays the same. But <code>fund_entries_id</code> is found via <code>fiscal_prd_end_date</code> in the <code>fund_entries</code> table for each insert.</p>
  
  <p>My tables look like this:</p>
  
  <pre><code>CREATE TABLE public.fund_data
  (
      id bigint NOT NULL DEFAULT nextval('fund_data_id_seq'::regclass),
      fund_entries_id integer NOT NULL,
      fund_val numeric,
      bbg_pulls_id integer,
      wh_calc_id integer,
      CONSTRAINT fund_data_pkey PRIMARY KEY (id),
      CONSTRAINT fund_data_fund_entries_id_bbg_pulls_id_key UNIQUE (fund_entries_id, bbg_pulls_id),
      CONSTRAINT fund_data_fund_entries_id_wh_calc_id_key UNIQUE (fund_entries_id, wh_calc_id),
      CONSTRAINT fund_data_bbg_pulls_id_fkey FOREIGN KEY (bbg_pulls_id)
          REFERENCES public.bbg_pulls (id) MATCH SIMPLE
          ON UPDATE NO ACTION
          ON DELETE NO ACTION,
      CONSTRAINT fund_data_fund_entries_id_fkey FOREIGN KEY (fund_entries_id)
          REFERENCES public.fund_entries (id) MATCH SIMPLE
          ON UPDATE NO ACTION
          ON DELETE NO ACTION,
      CONSTRAINT fund_data_wh_calc_id_fkey FOREIGN KEY (wh_calc_id)
          REFERENCES public.wh_calc (id) MATCH SIMPLE
          ON UPDATE NO ACTION
          ON DELETE NO ACTION
          DEFERRABLE,
      CONSTRAINT fund_data_check CHECK ((wh_calc_id IS NULL) &lt;&gt; (bbg_pulls_id IS NULL)),
      CONSTRAINT fund_data_check1 CHECK (NOT (wh_calc_id IS NULL AND fund_val IS NULL))
  )
  
  CREATE TABLE public.fund_entries
  (
      id integer NOT NULL DEFAULT nextval('fund_entries_id_seq'::regclass),
      fiscal_prd_end_date date NOT NULL,
      company_id integer NOT NULL,
      ern_dt_id integer NOT NULL,
      CONSTRAINT fund_entries_pkey PRIMARY KEY (id),
      CONSTRAINT fund_entries_company_id_fiscal_prd_end_date_key UNIQUE (company_id, fiscal_prd_end_date),
      CONSTRAINT fund_entries_ern_dt_id_key UNIQUE (ern_dt_id),
      CONSTRAINT fund_entries_company_id_fkey FOREIGN KEY (company_id)
          REFERENCES public.company (id) MATCH SIMPLE
          ON UPDATE NO ACTION
          ON DELETE NO ACTION,
      CONSTRAINT fund_entries_ern_dt_id_fkey11 FOREIGN KEY (ern_dt_id)
          REFERENCES public.ern_dt (id) MATCH SIMPLE
          ON UPDATE NO ACTION
          ON DELETE NO ACTION
  )
  </code></pre>
  
  <p>I am currently considering using <code>WITH</code> statements for the selects and access the <code>fund_entries_id</code> from the <code>WITH</code> queries. Not sure that is the cleanest way to do that.</p>
  
  <p>Or maybe something like this: 
  <a href="https://stackoverflow.com/questions/34688176/one-insert-with-multiple-select?utm_medium=organic&amp;utm_source=google_rich_qa&amp;utm_campaign=google_rich_qa">One INSERT with multiple SELECT</a></p>
  
  <p>Not sure how to implement that though.</p>
  
  <p>Thanks!</p>