title: Get table ID after insert with ColdFusion and MySQL
body: <p>My normal process for inserting into one table and then getting the ID back so that I can insert into another table is like this in MSSQL:</p>
  
  <pre><code>DECLARE @transactionKey uniqueidentifier
  SET @transactionKey = NEWID()
  
  INSERT INTO transactions(transactionKey, transactionDate, transactionAmount)
      VALUES(@transactionKey, '#transactionDate#', '#transactionAmount#')
  
  DECLARE @transactionID int
  SELECT @transactionID = transactionID
      FROM transactions
      WHERE transactionKey = @transactionKey
  
  
  INSERT INTO transactionItems(transactionID, itemID, itemAmount)
      VALUES(@transactionID, '#itemID#', '#itemAmount#')
  
  
  SELECT @transactionID as transactionID
  </code></pre>
  
  <p>My question is 2 parts. First, is this the best way to do this? I read that there is a chance that the GUID changes on me and I end up with a invalid GUID in the second table. I am assuming the chances of that are very slim and I have been doing this for years on various projects and have never had a problem.</p>
  
  <p>The second part of my question is does something like this work in MySQL? I am starting to work on a new project using MySQL and I am not exactly sure the best way to do this. I have normally only worked on MSSQL in the past.</p>
  
  <p>I am using CF9 and MySQL on this new project.</p>
  
  <p>Any help on this would be great.</p>
  
  <p>Thanks in advance.</p>