Creative solution for modifying/ALTER VIEW

• ltrain1007

  Newbie

  Points: 1

  More actions

  October 8, 2009 at 3:36 pm

  #211999

  I have a view that monthly adds a Union statement to the end of the view. It is always the same line, with exception being the table name.

  Heres what I want to be able to do:

  APPEND VW_SLS

  UNION SELECT * FROM SLS_CURRENT_MONTH

  Obviously that statment wont work, but thats the idea. I may be over thinking it, any suggestions?

• arun.sas

  SSChampion

  Points: 11831

  More actions

  October 8, 2009 at 9:58 pm

  #1063847

  SELECT * FROM VW_SLS

  UNION ALL

  SELECT * FROM SLS_CURRENT_MONTH

  For the permanent scenario, alter the view table.

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  October 8, 2009 at 10:44 pm

  #1063855

  Here's a start:

  DECLARE @sql AS NVARCHAR(MAX)

  SELECT @sql = VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'YourViewName'

  SELECT @sql = @sql + '

  UNION SELECT * FROM SLS_CURRENT_MONTH'

  BEGIN TRANSACTION

  BEGIN TRY

  DROP VIEW [YourViewName]

  EXEC(@sql)

  COMMIT TRANSACTION

  END TRY

  BEGIN CATCH

  PRINT 'Error ('+ERROR_NUMBER()+'): '+ERROR_MESSAGE()

  ROLLBACK TRANSACTION

  END CATCH

  Be aware that semicolons in your View definitions would require some more complex changes.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]