Union Construction Dynamics - 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 2005 Forums

Transact-SQL (2005)

Union Construction Dynamics

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2010-07-29 : 20:08:30

I have created a number of tables I want to "stack" on a report. I use UNION ALL to do this and it works great. However, what happens when a table is EMPTY? Is there a way to test for record count greater than 0 before putting it in the stack?

For example,in the code below, what if there are no rows in the #S_CURRENT and #DIFFERENCE tables? How can I sidestep adding them? How can I dynamically test the table count before I add a table?

IF OBJECT_ID('TEMPDB..#S_SUMMARY') IS NOT NULL
DROP TABLE #S_SUMMARY

SELECT * INTO #S_SUMMARY
FROM #S_CURRENT UNION ALL
SELECT * FROM #S_PREVIOUS UNION ALL
SELECT * FROM #S_DIFFERENCE UNION ALL
SELECT * FROM #S_BUDGET UNION ALL
SELECT * FROM #S_BUDGET_DIFF UNION ALL
SELECT * FROM #S_BUDGET_PCT
GO

Thanks!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-29 : 23:12:06

You could use dynamic SQL, or pop all the data into a holding table, but really, what's the point? The test for empty tables is exactly as expensive as just including it in the statement.

You test for empty tables like this

IF EXISTS(SELECT * FROM yourTable)

but again, I can't see any reason to bother.

If it matters for some reason that I can't fathom, you can first create the target table, then

IF Exists(SELECT * FROM #S_PREVIOUS)
BEGIN
	INSERT #S_SUMMARY
	SELECT * FROM #S_PREVIOUS
END

IF Exists(SELECT * FROM #S_DIFFERENCE)
BEGIN
	INSERT #S_SUMMARY
	SELECT * FROM #S_DIFFERENCE
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources