title: Deterministic GUID generation in T-SQL
body: <p>I have to prepare a script that selects data from a couple of tables and inserts new rows in the table in which some sort of many-to-many relationship is stored.</p>
  
  <p>So, basically, I have two tables with <code>uniqueidentifier</code> field as a primary key and another table that stores two foreign keys from these tables and some additional data. They primary key of this table is the <code>uniqueidentifier</code> as well.</p>
  
  <pre><code>INSERT ResultTable ([primaryKey],[foreignA], [foreignB])
      SELECT  newid(), /* &lt;- Can't have it! */
              @foreignKeyA,
              fb.[primaryKey]
      FROM foreignTableB as fb
  </code></pre>
  
  <p>The problem is that I can't use <code>newid()</code> to generate GUIDs for my generated data. One of the requirements is that the script produces identical results (primary keys included) when it's ran on identical tables. So I have to come up with a script that will insert new rows with <code>uniqueidentifier</code> value that is generated based on two other <code>uniqueidentifier</code> values. </p>
  
  <p>Now, I don't know of any functions that provide mapping from one GUID to another. Probably there aren't any. But I still hope to hear some proposals and advices.</p>
  
  <p>Also:</p>
  
  <ul>
  <li>We can assume that any pair of foreign keys is unique throughout the table</li>
  <li>I can't change the type of primary key fields in any of the tables mentioned</li>
  <li>It's mostly SQL Server 2005/2008 but some might run SQL Server 2000</li>
  </ul>
  
  <p>Thanks in advance.</p>