title: SQL Server: unique key for batch loads
body: <p>I am working on a data warehousing project where several systems are loading data into a staging area for subsequent processing.  Each table has a "loadId" column which is a foreign key against the "loads" table, which contains information such as the time of the load, the user account, etc.</p>
  
  <p>Currently, the source system calls a stored procedure to get a new loadId, adds the loadId to each row that will be inserted, and then calls a third sproc to indicate that the load is finished.</p>
  
  <p>My question is, is there any way to avoid having to pass back the loadId to the source system?  For example, I was imagining that I could get some sort of connection Id from Sql Server, that I could use to look up the relevant loadId in the loads table.  But I am not sure if Sql Server has a variable that is unique to a connection?</p>
  
  <p>Does anyone know?</p>
  
  <p>Thanks,</p>