How to show the updates that were made

• SQLTestUser

  Hall of Fame

  Points: 3997

  More actions

  March 4, 2011 at 2:11 pm

  #247738

  So i am updating a table as

  Update TableA

  SET columnA = Column B

  where ColumnA <> ColumnB

  and the sql server shows it as 17 rows were affected but what 17 rows in that table are updated is not displayed, is there a way i can display the updated rows as well, in other case the sql server management studio shows reuslts as

  1 row updated

  2 rows updated

  but how can i show what all was updated. can i use the print command to show the update, or is there a setting is management studio that can show us the update

  thanks

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  March 4, 2011 at 2:28 pm

  #1294889

  You're looking for the output clause. Check out these links:

  Overview: http://msdn.microsoft.com/en-us/library/ms177564.aspx

  Usage in Update: http://msdn.microsoft.com/en-us/library/ms177523.aspx

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 4, 2011 at 2:47 pm

  #1294900

  CREATE TABLE #temp (

  NEWVALUE varchar(100),OLDVALUE varchar(100) )

  Update MYTABLE

  SET COLUMNA = COLUMNB

  OUTPUT

  INSERTED.COLUMNA,

  DELETED.COLUMNA

  INTO #temp(NEWVALUE,OLDVALUE)

  where COLUMNA <> COLUMNB

  SELECT * FROM #temp

  drop table #temp

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!