SQL server determine which row was inserted?

Question

I have a sql query which inserts a lot of new rows in table and updates a lot of old rows.
Is there a way to determine all rows which where inserted?

All rows that exist in the table were technically inserted. You need to specify criteria for us to work with. — Khan, Apr 24 '12 at 13:53
@Jeff: The updated rows are also added to the deleted table before they are added to the inserted table. http://msdn.microsoft.com/en-us/library/aa214435%28v=sql.80%29.aspx Maybe that'll help to distinguish between both. — Tim Schmelter, Apr 24 '12 at 13:55
See [Sql server 2005 knowing new record is inserted](http://stackoverflow.com/questions/9243389/sql-server-2005-knowing-new-record-is-inserted/9247000#9247000) — John Dewey, Apr 24 '12 at 14:05

score 2 · Accepted Answer · edited May 23 '17 at 11:55

2

Below is by Andy Irving:

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.

edited May 23 '17 at 11:55

Community

answered Apr 24 '12 at 13:58

NaN

score 0 · Answer 2 · answered Apr 24 '12 at 13:53

0

Hi did you try something like execute

SELECT @@IDENTITY

after execute INSERT statement?

Take a look at this

answered Apr 24 '12 at 13:53

r4nd0m

score 0 · Answer 3 · answered Apr 24 '12 at 13:58

0

@@IDENTITY is deprecated, You would be recommended to use SCOPE_IDENTITY()

You can also determine the inserted items via the inserted table

answered Apr 24 '12 at 13:58

MrZulu

3 Answers3