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

by

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]
;

More Related Contents:

  1. Inserting multiple rows in a single SQL query? [duplicate]
  2. Add Foreign Key relationship between two Databases
  3. How to insert Arabic characters into SQL database?
  4. Calculate a Running Total in SQL Server
  5. How can I do an UPDATE statement with JOIN in SQL Server?
  6. Not equal != operator on NULL
  7. SET NOCOUNT ON usage
  8. How can I get column names from a table in SQL Server?
  9. How can I select the first day of a month in SQL?
  10. Subquery using Exists 1 or Exists *
  11. Select statement to find duplicates on certain fields
  12. Is it possible to specify condition in Count()?
  13. Getting only Month and Year from SQL DATE
  14. WHERE Clause vs ON when using JOIN
  15. Is it possible to select sql server data using column ordinal position
  16. how can I Update top 100 records in sql server
  17. @@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity
  18. How To Create Table with Identity Column
  19. SQL query to find Nth highest salary from a salary table
  20. MS SQL “ON DELETE CASCADE” multiple foreign keys pointing to the same table?
  21. How to split string using delimiter char using T-SQL?
  22. Get the records of last month in SQL server
  23. I want to use CASE statement to update some records in sql server 2005
  24. SQL server select distinct rows using most recent value only
  25. Access to Result sets from within Stored procedures Transact-SQL SQL Server
  26. alternatives to REPLACE on a text or ntext datatype
  27. How to return a incremental group number per group in SQL
  28. Eliminate and reduce overlapping date ranges
  29. COALESCE Function in TSQL
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment