title: T-SQL performance issue with bulk insert in millions of data
body: <p>I have created a query which is doing a bulk insert of millions of rows of data.
  While running this query, I'm getting a temdb memory error.</p>
  
  <p>This is the query:</p>
  
  <pre><code>INSERT INTO ods.contact_method (cmeth_cust_id, cmeth_chan_type_id, cmeth_address_id, 
                                  cmeth_identifier, cmeth_active, cmeth_review_date, 
                                  cmeth_last_validated, cmeth_updatesrc_id, cmeth_updated_date) 
      SELECT 
          custpers_cust_id, 5, ad.adet_id, 
          COALESCE(street3, '') + ' ' + COALESCE(street2, '') + ' ' 
          + COALESCE(housenumber, '') + ' ' + COALESCE(street, ''), 
          CASE custpers_status 
             WHEN 'InActive' THEN 'N' 
             ELSE 'Y' 
          END, 
          Dateadd(year, 2, last_update_date), 
          last_update_date, 1, Getdate() 
      FROM   
          ods.address_detail (nolock) ad 
      JOIN 
          ods.customer_persona (nolock) cp ON cp.custpers_cust_id = ad.adet_updated_by 
      JOIN 
          ods.tempcust_address_insert (nolock)tp ON tp.bvoc = cp.custpers_bvoc_id 
      WHERE   
          NOT EXISTS (SELECT 1 
                      FROM ods.contact_method (nolock) cm 
                      WHERE cm.cmeth_cust_id = cp.custpers_cust_id 
                        AND cm.cmeth_address_id IS NOT NULL
                        AND ad.adet_id = cm.cmeth_address_id)
  </code></pre>
  
  <p>I need help optimizing this query; should I use Left join or not exists condition on millions of data for bulk insert?</p>