Create Union Query based on available tables in DB 1

[RoyceyBaby]

Hi,

I have an puzzle. I have worked out how I can check what tables I have in my database using the follwing SQL.

SELECT name FROM sysobjects WHERE type='U' AND left(name,3)='xyz'

This gives me the names of all of the tables I wish to union together.

Question is how can I contruct the SQL statement for the union query automatically.

Any help would be greatly appreciated.

Thanks,

Royce

 

[AWithers]

Not sure how you would do it via a union but you could do it by inserting the values from each into a temporary table using a loop/cursor based on your above query. In each loop use the table name to insert values into your teporary table and at the end select everything from the temporary table

Sorry haven't got any code

Andy

 

[SQLSister]

Unioning all the tables in a databases is not usually done. In a Union query, the fields have to match up in datatype, size and number which usually is not the case, so your dynamic procedure would also have to figure out which fields to use. Plus with the looping added in, this will probably be a slow process. Why do you want to do this, maybe we can suggest a better method for achieving your aim.

 

[RoyceyBaby]

Hi,

What I have is tables with sales for each of the period in a year. All of these tables have the same columns.

What I am trying to achieve is to output sales for the year by combining all of the tables together.

I believe cursors will achieve what I require but I have never used them before, like most things MSSQL7 has to offer. I have managed to mangle together the following code but I have errors which means I cannot save the SP.

The error is:
ADO error: Column or parameter #-3: Specified column width too large for data type

SQL.
Parameter '

SQL'has an invalid data type

Here is what I have written so far

---------------------------------------------------------
Declare @Fields nvarchar(500)
Declare @TableName nvarchar(30)
Declare @SQL nvarchar(8000)


CREATE TABLE #TT
(
SalesByKitID INT
)


DECLARE CursorVar SCROLL CURSOR FOR
SELECT name FROM sysobjects WHERE type='U' AND left(name,18)='xyz_SBK_Final_2002'

OPEN CusorVar
FETCH FIRST FROM CursorVar Into @TableName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM CusorVar Into @TableName
Set @SQL='SELECT SalesByKitID INTO #TT FROM ' + @TableName
Exec(@SQL)
Fetch Next FROM CusorVar Into @TableName
END
END
CLOSE CursorVar
DEALLOCATE CursorVar


Set @SQL='SELECT * FROM #TT'
Exec (@SQL)
------------------------------------------------------

As I have previousily mentioned, I have no experience of writing cursors, so it is probably something glaringly obvious.


Many thanks,

Royce

 

[sunila7]

Hi,

Try this code.....

Hope it helps


Declare @Fields varchar(500)
Declare @TableName varchar(30)
Declare @SQL varchar(8000)
SET @SQL = ''
DECLARE CursorVar CURSOR FOR
SELECT name FROM sysobjects WHERE type='U' AND left(name,2)='xyz_SBK_Final_2002'

OPEN CursorVar

FETCH NEXT FROM CursorVar Into @TableName
WHILE (@@FETCH_STATUS =0)
BEGIN
Set @SQL=@SQL + 'SELECT SalesByKitID FROM ' + @TableName + ' UNION '
print @tableName
Fetch Next FROM CursorVar Into @TableName
END
CLOSE CursorVar
DEALLOCATE CursorVar
SET @SQL = LEFT(@SQL,LEN(@SQL)-5)
Exec (@SQL)

Sunil

 

[tlbroadbent]

Here is a simple cursorless method.

Declare @sql varchar(8000)
Set @sql=''

Select @sql=@sql + 'Select * From ' + name + char(10) + 'union' + char(10)
From Sysobjects
Where type='u'
and left(name,18)='xyz_SBK_Final_2002'

Select @sql=Left(@sql,len(@sql)-7)

Exec(@sql) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.