title: Update records based on inserted IDs and another non source column in SQL
body: <p>Probably there is already answer for it, but i couldn't find it... So i have 2 tables and data in third one. Lets name them (Source, Target and UpdateTarget).</p>
  
  <p>I need to insert records from Source to Target, then grab autoincremented IDs from Target and update UpdateTarget table with these IDs based on filters from Source table. I've tried to use OUTPUT, but it gives me an error:</p>
  
  <blockquote>
    <p>The multi-part identifier "s.EmployeeID" could not be bound.</p>
  </blockquote>
  
  <p>Here is my current SQL query:</p>
  
  <pre><code>CREATE TABLE dbo.target
  (
      id INT IDENTITY(1,1) PRIMARY KEY,
      employee VARCHAR(32)
  );
  
  CREATE TABLE dbo.source
  (
      id INT IDENTITY(1,1) PRIMARY KEY,
      employee VARCHAR(32),
      EmployeeID int
  );
  
  CREATE TABLE dbo.updateTarget
  (
      id INT IDENTITY(1,1) PRIMARY KEY,
      ExternalID int
  );
  
  DECLARE @MyTableVar TABLE
  (
      id INT,
      EmployeeID int
  );
  
      INSERT dbo.target (employee)
      OUTPUT 
          inserted.id, -- autoincremented ID
          s.EmployeeID -- here i got an error
          INTO @MyTableVar
      SELECT s.employee
      FROM dbo.source AS s
  
      UPDATE dbo.updateTarget
      SET ExternalID = data.ID
      FROM @MyTableVar data
      WHERE updateTarget.ID = data.EmployeeID
  
  
  DROP TABLE source
  DROP TABLE target
  DROP TABLE updateTarget
  </code></pre>
  
  <p>I don't have EmployeeID column in target table.</p>
  
  <p>Is there a way to achieve it without making two queries for each record? Or can you point me to existing answer if there are any?</p>
  
  <p>Thanks!</p>