How can I get results from two stored procs merged into one datatable?

I have two stored procs that I'm trying to merge into one datatable. One has a column named 'Branches' and the other one is returning a column name开发者_开发问答d 'LocationID.' How can I loop through the two stored proc results and insert them into one datatable.

You can store the results of a stored procedure in a table using the syntax INSERT myTable EXEC myStoredProc

Once you have the two results sets in your tables (be they temp tables, or otherwise), you can just use them in a JOIN...

INSERT rs1 EXEC sp1
INSERT rs2 EXEC sp2

INSERT
  myTable
SELECT
  blah
FROM
  rs1
INNER JOIN
  rs2
    ON rs1.foo = rs2.bah

NOTE:

As per my comment, the position in the results set is not enough to determine how the two results sets will join. You could define the tables rs1 and rs2 to have an IDENTITY column though, and use an ORDER BY in the StoredProcedures to ensure they're always inserted in the same order, and so always get the right Identity values.