Order of UNION ALL - 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)

Order of UNION ALL

Author

Topic

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-12-18 : 07:12:39

Hi there,

I have a stored procedure which returns

SELECT @header
UNION ALL
SELECT <some real stuff>

which is finally bcped into a file. Is there any guarantee that @header will always be the first row in my output?

Thanks,
Bjoern

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-18 : 07:33:20

No.

If you need a guaranteed order, use an order by statement

--
Gail Shaw
SQL Server MVP

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-12-18 : 07:47:15

Thanks, think I found a solution then:

SELECT Data
FROM(
	SELECT 1 AS IsHeader, @header as Data
	UNION ALL
	SELECT 0, <some real stuff>
) as HeaderFirst
ORDER BY IsHeader DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 08:52:32

You can also use a temp table with an identity (1, 1) column.

insert ##temp values (@header)

insert ##temp
select <cool stuff here>
from ...
order by...

And then BCP using query out

BCP "SELECT col1 FROM ##Temp order by identitycol" qoeryout ... ...

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources