SQL question: Combining records from two queries into one temp file

Question

Hello all.

I have two servers that contain subsets of the same information. I am running two queries now with a UNION statement. Then I get the records displayed on the screen.
I would like to combine the results in a temporary file and process them further.

How do I do that?

Here is what I constructed looks like now:

SELECT *
FROM server1.dbo.guests
WHERE reg_date >= '2022-01-01'

UNION

SELECT *
FROM server2.dbo.guests
WHERE reg_date >= '2022-01-01'

Thanks,

Deguza

Answer 1

You could use SELECT - INTO Clause (Transact-SQL) and the bcp utility to export your file for further inspection.

SELECT - INTO Clause (Transact-SQL)

SELECT *  
  INTO  #temp_UNION   
FROM  
(  
        SELECT     *  
    FROM         [#temp1]  
    UNION  
    SELECT     *  
    FROM         [#temp2]  
    UNION  
    SELECT     *  
    FROM         [#temp3]  
    UNION  
    SELECT     *  
    FROM         [#temp4]  
    UNION  
    SELECT     *  
    FROM         [#temp5]  
) a  

Cited from https://stackoverflow.com/questions/6456388/how-to-move-union-query-results-to-a-new-table

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16

bcp Utility

bcp yourdbo.temptable out C:\Correct Path\tempfile.csv -c -U username -S Azure.database.windows.net

https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver16

Answer 2

temporary file

What for a "temporary file"?
Do you mean a temporary table instead? The use a SELECT ... INTO statement, see
https://learn.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql?view=sql-server-ver16

Answer 3

You didn't make it clear what the temporary file is, and cte can also be implemented. I think the merge result can be placed in cte, and the result of cte merge is called when modifying data.

Bert Zhou