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

Try this:

insert into [table] ([data])output inserted.id, inserted.data into table2select [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 tusing [external_table] as son 1=0 --modify this predicate as necessarywhen not matched then insert (data)values (s.[col1])output inserted.id, s.[col2] into [table2];

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 intDECLARE MY_CURSOR CURSOR   LOCAL STATIC READ_ONLY FORWARD_ONLYFOR SELECT  ID FROM   [external_Table]OPEN MY_CURSORFETCH NEXT FROM MY_CURSOR INTO @OuterIDWHILE @@FETCH_STATUS = 0BEGIN 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 @OuterIDENDCLOSE MY_CURSORDEALLOCATE 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