title: SQL bulk insert into select
body: <p>Here's the situation</p>
  
  <p>I have an SP in SQL which works on two <code>Table type variables</code> which are used to perform bulk insertion in to <code>Mails &amp; attachments</code> respectively now they both have relationship in SQL of <code>one to many</code> of course on <code>MailId</code>, SP looks like this</p>
  
  <pre><code>MailInsertSP
    @Mails mailsTableVar
    @Attachements attachementsTableVar
  
  -- this query will insert mails &amp; generate MailIds
  insert into Mails
  ( mails table column list... )
  select
  value list
  from @Mails
  
  -- this query will insert attachments
  insert into Attachments
  (MailId, other columns...)
  select
  (select MailId from Mails where MailDate = TBL.MailDate) as MailId,
  other values
  from @Attachements as TBL
  </code></pre>
  
  <p>Till now <code>MailDate</code> was <code>unique</code> for mails so this code was working fine but now I have got a situation where <code>MailDate</code> are same in two rows and I don't have any other column which could be used as unique key, So what I need is some solution so that I can insert in Mails and get generated Ids which can be used in next insertion of attachments.</p>
  
  <p>It would be appreciable if solution would not have following</p>
  
  <ol>
  <li><p>Any kind of loops.</p></li>
  <li><p>Cursors.</p></li>
  </ol>