How to merge two stored procedure data

Question

I have 2 stored procedures, each SP returns 2 data set with same schema.

data are like

Stored procedure 1

Table1

ID       Name
1        Apple
2        Sony
3        DELL

Table2

OrderID  CustomerName
101        AB Stores
202        Sony World
301        PC House

Stored procedure 2

Table1

ID       Name
21        HP
32        LG
34        ACCER

Table2

OrderID  CustomerName
1301        X-PC World
2202        Your PC House
3301        Ajant Stores

Now I want to merge data like this inside other stored procedure

meargeData1 = SP1.Table1 + SP2.Table1

meargeData2 = SP1.Table2 + SP2.Table2

please suggest me hot I can achieve it .

score 0 · Answer 1 · edited May 23 '17 at 12:12

0

Collect the results of both procedures into a into a temporary table like in question.

Insert results of a stored procedure into a temporary table

edited May 23 '17 at 12:12

Community

1
1

answered Oct 29 '12 at 08:47

Preet Sangha

64,563
18
145
216

score 0 · Accepted Answer · answered Oct 29 '12 at 08:58

Solutions like OPENROWSET (as in the linked question the @PreetSangha included) don't cope with multiple result sets - they only give you the first result set.

Similarly, using a construct such as INSERT ... EXEC will cope with multiple result sets from stored procedures, but it will store all of the results in a single table - you won't be able to pull them apart. (There are other issues with INSERT ... EXEC that aren't worth going into here also)

About the only way I can see to do this is by writing some procedural code somewhere in a language that isn't TSQL. This could either be in whatever client applications you're building, or a CLR stored procedure. In either case, you'll have to perform the merge manually.

How to merge two stored procedure data

2 Answers2