While...Union - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

While...Union

Author

Topic

kindalost
Starting Member

4 Posts

Posted - 2011-06-15 : 10:24:30

I've searched everywhere for a method of combining *many* tables in a database, but everytime i search for something like

"union while loop sql server" all I see are responses telling folks this is a bad idea. My circumstances differ from all

of theirs, however...

I have a database with over 800 pertinent tables, all with the same formats, that I would like to be able to report on as

one recordset. Basically each client gets his own table(s), but each has identical columns/indexes/etc. I know this is

horrible database design, but it's the back end of a software suite we use - it isn't our design and we can't change it.

What i want to do is, generally, select the list of tables from information_schema.tables:

DECLARE @TableName NVARCHAR(256)

SET @TableName = ''
WHILE @TableName IS NOT NULL

BEGIN

SET @TableName =
(SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND QUOTENAME(TABLE_NAME) like 'i3[_]%'
AND QUOTENAME(TABLE_NAME) like '%[_]CH%'
AND QUOTENAME(TABLE_NAME) not like 'i3[_]test%'
)
--Union next table here?
END

Then I'd like to union each additional table to the last...perhaps make a view of it. I realize I might run into

performance issues with so many tables, but I have to at least try it - I can't think of another way to do any sort of

standardized reporting on this monster.

I'd appreciate any input.

Thanks!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-06-15 : 11:06:25

If you don't mind doing it as a two step process, you can write the SQL statement that generates a SQL statement and then run the generated SQL:[CODE]select 'select * from ' QUOTENAME(TABLE_SCHEMA) + N'.' + QUOTENAME(TABLE_NAME) + N' UNION ALL'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_NAME) like 'i3[_]%'
AND QUOTENAME(TABLE_NAME) like '%[_]CH%'
AND QUOTENAME(TABLE_NAME) not like 'i3[_]test%'[/CODE]You'll need to remove the last 'UNION ALL' from the script generated but the rest of it would provide the bulk of the View you wanted to create.

If, in SSMS, you change the configuration to send the "Results to Text", you can preserve the formatting of special characters, such as Tabs, Line Feeds, Carriage Returns, et al, if you'd prefer.
With a little massaging, you could use dynamic SQL to actually create the view, also.

=======================================
I have never met a man so ignorant that I couldn't learn something from him. -Galileo Galilei, physicist and astronomer (1564-1642)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-15 : 11:08:16

As an approach:
This example takes all tables beginning with A or B.

exec sp_msforeachtable 
@command1=' if parsename(''?'',1) like ''A%'' OR
               parsename(''?'',1) like ''B%''
            print ''select * from ? union all'' '

Then you can copy and paste the printed output (it is the union query), remove the last UNION ALL and then run it.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-15 : 11:09:07

No, you're never too old to Yak'n'Roll if you're too young to die.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-15 : 11:58:53

to add to the above solutions: will your number of customers hence tables stay static or will it grow and even shrink at times?

If you don't have the passion to help people, you have no passion

kindalost
Starting Member

4 Posts

Posted - 2011-06-15 : 12:12:48

quote:
Originally posted by yosiasz

to add to the above solutions: will your number of customers hence tables stay static or will it grow and even shrink at times?

If you don't have the passion to help people, you have no passion

New tables are added regularly, so it isn't a static list.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-15 : 12:46:07

how often? once a week, one a month, once a day? in that case I would suggest you create a job that runs every day with the script provided to you above.

If you don't have the passion to help people, you have no passion

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-15 : 16:59:19

You might be able to create a DDL Trigger that could re-create the view. I haven't tried myself as I'd never get into that position, but could be "fun" to play with.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-15 : 17:03:32

You will be far better off having a dedicated table you can consolidate data into, rather than creating/recreating a view each time a new table is added. With this number of sources, you are essentially creating a data warehouse.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-15 : 17:31:40

robvolk is right on the money. trying to create another flawed design will only compound your problems later on. I would do an ETL to dump all data into one table with proper indexing and all = datawarehouse as robvolk has mentioned. Then you have full control

If you don't have the passion to help people, you have no passion

kindalost
Starting Member

4 Posts

Posted - 2011-07-07 : 16:10:26

Thanks everyone for your ideas. I ended up doing this:

exec sp_msforeachtable
@command1=' if parsename(''?'',1) like ''I3[_]%'' AND
parsename(''?'',1) like ''%[_]CH[0-99]'' AND
parsename(''?'',1) not like 'i3[_]test%'' AND
parsename(''?'',1) not like ''i3[_]deleteme%'' AND
parsename(''?'',1) not like ''i3[_]is[_]number%'' AND
parsename(''?'',1) not like ''i3[_]kevintest%''
insert into [dialer2].dbo.[callhistory]
select * from ?
where cast(convert(varchar(8), callplacedtime, 1) as datetime) =
cast(convert(varchar(8), getdate() -1, 1) as datetime)'

The only problem i now have is putting this in a job, because the above generates many, many errors. It works, and the new table gets populated, but even though the above criteria only points to about 800 tables it generates over 1000 errors...all the same error as well.

Msg 207, Level 16, State 1, Line 9
Invalid column name 'callplacedtime'.
Msg 213, Level 16, State 1, Line 9
Insert Error: Column name or number of supplied values does not match table definition.

Recall that all tables have the same format, and the field 'callplacedtime' exists in every one of them. Why it's throwing that error, then, i have no idea - but it's preventing the job from succeeding.

Any thoughts?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-07 : 18:15:16

sp_msforeachtable parses the statement for each table, even though an IF statement may prevent it from running for that table. To work around that error you'll need to do this:

exec sp_msforeachtable 
@command1=' if parsename(''?'',1) like ''I3[_]%'' AND
parsename(''?'',1) like ''%[_]CH[0-99]'' AND
parsename(''?'',1) not like ''i3[_]test%'' AND
parsename(''?'',1) not like ''i3[_]deleteme%'' AND
parsename(''?'',1) not like ''i3[_]is[_]number%'' AND
parsename(''?'',1) not like ''i3[_]kevintest%''
insert into [dialer2].dbo.[callhistory] 
exec(''select * from ?
where cast(convert(varchar(8), callplacedtime, 1) as datetime) =
cast(convert(varchar(8), getdate() -1, 1) as datetime)'')'

You'll also get better performance by not CONVERTing date columns to varchar:

exec sp_msforeachtable 
@command1=' if parsename(''?'',1) like ''I3[_]%'' AND
parsename(''?'',1) like ''%[_]CH[0-99]'' AND
parsename(''?'',1) not like ''i3[_]test%'' AND
parsename(''?'',1) not like ''i3[_]deleteme%'' AND
parsename(''?'',1) not like ''i3[_]is[_]number%'' AND
parsename(''?'',1) not like ''i3[_]kevintest%''
insert into [dialer2].dbo.[callhistory] 
exec(''select * from ?
where callplacedtime between dateadd(day, datediff(day, 0, getdate()), -1)
and dateadd(second, -1, dateadd(day, datediff(day, 0, getdate()), 0))'')'

kindalost
Starting Member

4 Posts

Posted - 2011-07-08 : 10:57:55

Thank you, thank you, thank you! That did the job perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources