How can I INSERT data into two tables simultaneously in SQL Server?

I was also struggling with this problem, and find that the best way is to use a CURSOR.

I have tried Denis solution with OUTPUT, but as he mentiond, it's impossible to output external columns in an insert statement, and the MERGE can't work when insert multiple rows by select.

So, i've used a CURSOR, for each row in the outer table, i've done a INSERT, then use the @@IDENTITY for another INSERT.

DECLARE @OuterID int

DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT  ID FROM   [external_Table]

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @OuterID

WHILE @@FETCH_STATUS = 0
BEGIN 
INSERT INTO [Table]   (data)
    SELECT data
    FROM     [external_Table] where ID = @OuterID 

    INSERT INTO [second_table] (FK,OuterID)
    VALUES(@OuterID,@@identity)

    FETCH NEXT FROM MY_CURSOR INTO @OuterID
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

Keep a look out for SQL Server to support the 'INSERT ALL' Statement. Oracle has it already, it looks like this (SQL Cookbook):

insert all
  when loc in ('NEW YORK', 'BOSTON') THEN
   into dept_east(deptno, dname, loc) values(deptno, dname, loc)
  when loc in ('CHICAGO') THEN
   into dept_mid(deptno, dname, loc) values(deptno, dname, loc)
  else
   into dept_west(deptno, dname, loc) values(deptno, dname, loc)
select deptno, dname, loc
  from dept

Try this:

insert into [table] ([data])
output inserted.id, inserted.data into table2
select [data] from [external_table]

UPDATE: Re:

Denis - this seems very close to what I want to do, but perhaps you could fix the following SQL statement for me? Basically the [data] in [table1] and the [data] in [table2] represent two different/distinct columns from [external_table]. The statement you posted above only works when you want the [data] columns to be the same.

INSERT INTO [table1] ([data]) 
OUTPUT [inserted].[id], [external_table].[col2] 
INTO [table2] SELECT [col1] 
FROM [external_table] 

It's impossible to output external columns in an insert statement, so I think you could do something like this

merge into [table1] as t
using [external_table] as s
on 1=0 --modify this predicate as necessary
when not matched then insert (data)
values (s.[col1])
output inserted.id, s.[col2] into [table2]
;