cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
closcher
Frequent Visitor

Append two SQL Queries

Hi I I'm executing two SQL queries from two different databases. I need to append them into one single result set/query. Thanks for the help.
6 REPLIES 6
KlausAmbrass
Advocate I
Advocate I

The question is a bit broadly put - it depends very much on what you want to do with the data once you have merged it.

Provided you have access and rights to both tables on the same server, I think you propably would use a UNION query:
-----------------------------------------------------------------------------------
(
SELECT TOP (10) [id]
,[field1] as title
,[field2] as name
,'source1' as dataSource
FROM [Database1].[dbo].[Table1]
)

UNION

(
SELECT TOP (10) [ID]
,[fieldx] as title
,[fieldy] as name
,'source2' as dataSource
FROM [Database2].[dbo].[Table2]
)
-----------------------------------------------------------------------------------

The important thing is that the fields in both queries *return* the same names og types of data.
Above, the first query gets first 10 records form DB1, table1 and merges it with the second query from DB2, table2.
You are free to include and rename any fields, as long as the names and type match in both querys (that's a requirement for UNION to work).
The [Source] is merely included to show from which DB/Table the record comes from. Delete if not important.

Good luck

Best regards
/Klaus

Hi Klaus, 

 

Thanks so much for your reply. I'm generating the queries in flow so I don´t think it will allow calling two databases in the same step since I have to select the database I'm calling in each step. Or will it allow me if I have the connections created to both databases?

 

So I could generate the two queries in two steps and then UNION them in a third step. 

Any better thoughts?

 

closcher_0-1660227350525.png

 

KlausAmbrass
Advocate I
Advocate I

Hm... I have done it only in Desktop. Here, you can UNION two queries from two databases in the same SQL, if the databases reside on the same server (and you have the needed access rights).

Cloud task: I'm not sure there is a UNION function/operation. You might have to pull two data sets and then merge/add them. Propably using JSON.

Thank you Klaus. My knowledge in JSON is very limited. Would you be able to picture the steps and the code/formula?

 

Thanks again. 

KlausAmbrass
Advocate I
Advocate I

Thanks Klaus!

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (3,704)