Perform Union of tables based on table name and insert table name into results

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

Who is Participating?
vs1784Connect With a Mentor Commented:
yes it is a good one.

You can use cursor for generaating Select script.
SELECT table1.*, 'table1' AS TableName FROM table1
SELECT table2.*, 'table2' AS TableName FROM table2
SELECT table3.*, 'table3' AS TableName FROM table3

Yes you can put it in a view.
BHRAuthor Commented:
Thanks.  I think that would have worked... although I'd have to enter each table separately.  

It turns out we had an existing solution to this problem that I didn't recall existed.  We have a second table that lists all the table names that I wanted to use in the "union", and we used that table in conjunction with a cursor.  Below is the code we are using.  Works just swimmingly.

Thanks anyway.
Use InTouch
DECLARE @dbtablename varchar(255)
DECLARE @SQL varchar(4000)
DECLARE scriptname_cursor 
	CURSOR FOR Select scripttable from LuContractForIS Where enabled = 1
--Delete from Script_ALL_Intouch
Open scriptname_cursor
Fetch Next from scriptname_cursor INTO @dbtablename
while @@FETCH_STATUS = 0
--	Print @dbtablename 
	SELECT @sql = 'Insert into Script_ALL_Intouch_021108
	Select EventType,MinimumAnswer,NewQuestionIdentifier,Type,SortOrder,Enabled,OmitLabel,ScriptText,RowHeight,FontStyle,AnswerName,GoToQuestion,AnswerType,Mandatory,AllowTypingInDropDown,MaxFieldLength,DefaultAnswer,SaveToField,SaveToTable,LookupField,DisplayField,LookupTable,Query,DependentFieldName,Script_Table='+ char(39) + @dbtablename + char(39) +'
		from [' + @dbtablename +'] Where Enabled = 1'
--	Print @sql 
	exec (@sql)
	Fetch Next from scriptname_cursor INTO @dbtablename
close scriptname_cursor
Deallocate scriptname_cursor
--select * into Script_ALL_Intouch_021108 from Script_ALL_Intouch

Open in new window

Closed, 500 points refunded.
EE Moderator
All Courses

From novice to tech pro — start learning today.