capture the output of inserts

Question

capture the output of inserts

TRACEY-320982

SSChampion

Points: 13653
More actions
June 26, 2010 at 10:17 am

#387222

Is there a way to show the record updated
Code:
INSERT INTO MusicArtists (FirstName, LastName, Instrument)
VALUES ('Bobby', 'Lee', 'fiddle');
Output:
(1 row(s) affected)
Inserted of the 1 row affected
Iss it possible to show the record rather then 1 row affected.
I am putting into a log table but as it runs I would like to see the record rather than displaying print
The update is hugh and after 1000 records i get
The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.
How to increase?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Use the OUTPUT clause:

declare @MusicArtists TABLE (RowID INT IDENTITY, FirstName varchar(30), LastName varchar(30), Instrument varchar(30));

declare @MusicArtistsAudit TABLE (RowID INT, FirstName varchar(30), LastName varchar(30), Instrument varchar(30));

INSERT INTO @MusicArtists (FirstName, LastName, Instrument)

OUTPUT inserted.RowID, inserted.FirstName, inserted.LastName, inserted.Instrument INTO @MusicArtistsAudit

VALUES ('Bobby', 'Lee', 'fiddle');

select * from @MusicArtists

select * from @MusicArtistsAudit

Note that this also will get the value of the IDENTITY column - this is a better was over scope_identity() or @@identity to get the identity value.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 2

Wow didn't know you could do that. Thanks.

Do you know how to get rid of the exception error at the end so I can process more records.

WayneS SSC Guru Points: 95460 More actions · Answer 3

Do all of your inserts first, then finally select from the output table. (instead of insert 1, select 1)

That error is an SSMS (GUI) message - it only displays results from the first 1000 result sets. I don't know of any way to work around that message, other than to reduce the number of result sets that you are getting.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 4

Thanks for all your help. If i select display in text mode it appears to be working.

Cheers