title: insert with subquery and select
body: <p>I would like to insert rows into a table using  a select statement to query specific data but use data from a different table as part of the insert.  EXmaple:
  Table A: Clients  where data is being queried and copied
  Table B: MailOptOut where data is being inserted</p>
  
  <p>I want to insert in MailOptOut table two values, a hardcoded field 'Summer Promotion' and then the acct# from the clients table (client.acct_no)</p>
  
  <p>Here is my code that isn't working:</p>
  
  <pre><code>INSERT INTO PL00.DBO.mailcoptout (MC_NAME, ACCT_NO)
  VALUES 
  ('Summer Service Promo',  client.acct_no),
  ('Referral Rewards Doubled', client.acct_no),
  ('Holiday Decorating 1',  client.acct_no),
  ('Holiday Decorating 2', client.acct_no)
  select client.acct_no, mailcoptout.* from plshared.dbo.client  left join PL00.DBO.mailcoptout on mailcoptout.ACCT_NO = client.ACCT_NO
  where client.U_SOLICIT = 'y'
  and client.acct_no = '131335'
  and client.INACTIVE &lt;&gt; 'y'
  and mailcoptout.MC_NAME is null
  </code></pre>