Append two result sets - 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)

Append two result sets

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-16 : 06:37:39

Hi everyone,
This is vaibhav again.

i have one result set which some columns are there, data for them is coming from many tables
and result set of select statement looks like
ID column1 column2
1 abc1 xyz1
2 abc2 xyz2
3 abc3 xyz3
1 abc4 xyz4

now i need to append some ids which will come by another result set
those ids should be append with the above result set and with null values for column1 and column2.

what might be way to do this.

please help me.

Thanks in advance

Vabhav T

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-16 : 06:45:40

Sorry i forgot to tell one condition that those id from second result set should not be in above result set.

Vabhav T

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-16 : 07:08:56

Try this:
-- your first select
select
ID,
column1,
column2
from table where ...
-- combining second select
UNION
select
ID,
NULL,
NULL
from other_table t1
left join (..your_first_select)dt
on t1.ID = dt.ID
where dt.ID is null

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

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-16 : 07:45:27

That logic i thought but first select very heavy i dont want to repeat it

Vabhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 12:25:26

quote:
Originally posted by vaibhavktiwari83

That logic i thought but first select very heavy i dont want to repeat it

Vabhav T

then put it in a temporary table first and then use it in second query like


-- your first select
select
ID,
column1,
column2 into #yourtemptable
from table where ...


now second query

select columns
from #yourtemptable
UNION
select
ID,
NULL,
NULL
from other_table t1
left join yourtemptable dt
on t1.ID = dt.ID
where dt.ID is null

drop table yourtemptable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-17 : 02:59:06

Thanks visakh16 for the help but that i done with cte or temporary table but performance wise its more heavy than previous where i was using full outer join.

any ways thanks because there is not any alternate to do this

Vabhav T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources