title: How can I INSERT data into two tables simultaneously in SQL Server?
body: <p>Let's say my table structure looks something like this:</p>
  
  <pre><code>CREATE TABLE [dbo].[table1] (
      [id] [int] IDENTITY(1,1) NOT NULL,
      [data] [varchar](255) NOT NULL,
      CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([id] ASC)
  )
  
  CREATE TABLE [dbo].[table2] (
      [id] [int] IDENTITY(1,1) NOT NULL,
      [table1_id] [int] NOT NULL,
      [data] [varchar](255) NOT NULL,
      CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED ([id] ASC)
  )
  </code></pre>
  
  <p>The <code>[id]</code> field of the first table corresponds to the <code>[table1_id]</code> field of the second. What I would like to do is insert data into both tables in a single transaction. Now I already know how to do this by doing INSERT-SELECT-INSERT, like this:</p>
  
  <pre><code>BEGIN TRANSACTION;
  DECLARE @id [int];
  INSERT INTO [table1] ([data]) VALUES ('row 1');
  SELECT @id = SCOPE_IDENTITY();
  INSERT INTO [table2] ([table1_id], [data]) VALUES (@id, 'more of row 1');
  COMMIT TRANSACTION;
  </code></pre>
  
  <p>That's all good and fine for small cases like that where you're only inserting maybe a handful of rows. But what I need to do is insert a couple hundred thousand rows, or possibly even a million rows, all at once. The data is coming from another table, so if I was only inserting it into a single table, it would be easy, I'd just have to do this:</p>
  
  <pre><code>INSERT INTO [table] ([data])
  SELECT [data] FROM [external_table];
  </code></pre>
  
  <p>But how would I do this and split the data into <code>[table1]</code> and <code>[table2]</code>, and still update <code>[table2]</code> with the appropriate <code>[table1_id]</code> as I'm doing it? Is that even possible?</p>