How to insert multiple records and get the identity value?

by

Use the ouput clause from 2005:

DECLARE @output TABLE (id int)

Insert into A (fname, lname)
OUTPUT inserted.ID INTO @output
SELECT fname, lname FROM B

select * from @output

now your table variable has the identity values of all the rows you insert.

More Related Contents:

  1. Add a column with a default value to an existing table in SQL Server
  2. Simulating group_concat MySQL function in Microsoft SQL Server 2005?
  3. How can I do an UPDATE statement with JOIN in SQL Server?
  4. The multi-part identifier could not be bound
  5. How to return multiple values in one column (T-SQL)?
  6. Fastest way to update 120 Million records
  7. Can a Check constraint relate to another table?
  8. Is it possible to select sql server data using column ordinal position
  9. How to find current transaction level?
  10. How to Replace Multiple Characters in SQL?
  11. SELECT FOR UPDATE with SQL Server
  12. EXISTS vs JOIN and use of EXISTS clause
  13. SQL comma-separated row with Group By clause
  14. How to copy a huge table data into another table in SQL Server
  15. How to split string using delimiter char using T-SQL?
  16. Should I use SQL_Variant data type?
  17. Performance of SQL Server 2005 Query
  18. OPENXML with xmlns:dt
  19. SQL server select distinct rows using most recent value only
  20. Script to save varbinary data to disk
  21. Measuring Query Performance : “Execution Plan Query Cost” vs “Time Taken”
  22. How to select date without time in SQL
  23. What is the internal representation of datetime in sql server?
  24. Eliminate and reduce overlapping date ranges
  25. Cumulative Total in MS Sql server [duplicate]
  26. SQL query to group by day
  27. Export table data from one SQL Server to another
  28. SQL can I have a “conditionally unique” constraint on a table?
  29. Memory effective way to read BLOB data in C#/SQL 2005
  30. SQL, How to Concatenate results?

Leave a Comment