Union or aggregate into table variable in SQL CMD MODE

Question

Is there a way to combine records into one table across databases (and on different servers) when using SQL CMD MODE in SSMS?

:Connect Database1
select firstname,lastname from table1
GO
:Connect Database2
select firstname,lastname from table1
GO
:Connect Database3
select firstname,lastname from table1
GO

How can I put all the results in one table?

No, they don't reside on the same server. Three different ones. — Rod, Apr 19 '23 at 21:06
then link them to one server and union them or join or what ever you need — nbk, Apr 19 '23 at 21:12
using python c# or such you need toget all tables and use the dataset or dataframe to combine them — nbk, Apr 19 '23 at 21:15

score 1 · Answer 1 · answered Apr 19 '23 at 21:16

Yes you could use variables to hold the servers, union the results and use select into, it would look something like:

:setvar serv1 "server1"
:setvar serv2 "server2"

:Connect $(serv1)
select column into #Result
from database.schema.table
union all
select column from $(serv2).database.schema.table

select * from #Result /* Temp table on server1 */

Union or aggregate into table variable in SQL CMD MODE

1 Answers1