I'd like to join multiple tables into a single result table as described below. All tables have the same fields and all the desired tables' names begin with the string "Script". Don't know if I can do the following using UNION or if I need some kind of cursor. Your help is appreciated.
(1) I'd like to insert the table name as an additional column in the result table so I can differentiate the data (by it's table of origin);
(2) there are MANY tables (about 20) that I'd like to join. all the desired tables' names begin with the string "script" - it'd be nice if I didn't have to enumerate each table name, but rather somehow select the tables based on this criteria
(3) can this be done as a view to reflect changes to the tables, or do I need to schedule this as a recurring job