How to combine the results of the union all queries ?

Question

How to combine the results of the union all queries ?

Naresh y 146

Hi team

How do i insert the below union logic into one test table, how to insert ,as per the logic ,i am trying to insert this records into one test table ,not able to insert, can some one help for this logic

;WITH cte
as(SELECT Firstpart,'0001-01-01' [date2],NULL quantity
FROM (SELECT ,ROW_NUMBER() OVER(PARTITION BY Firstpart ORDER BY Firstpart) rr
FROM #Sourcetable)t WHERE rr=1
UNION ALL
SELECT FROM #Output
UNION ALL
SELECT Firstpart,'9999-12-31',NULL
FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY Firstpart ORDER BY Firstpart) rr
FROM #Sourcetable)t WHERE rr=1)
SELECT Firstpart,CASE WHEN [date2] = '0001-01-01' THEN 'STA'
WHEN [date2] = '9999-12-31' THEN 'FAU' ELSE [date2] END [date], quantity
FROM cte
ORDER BY Firstpart,[date2], quantity

Thank you! in advance!

Tom Phillips 17,771 Reputation points

2021-11-10T18:18:37.45+00:00

Are you getting an error message? What exactly is happening?
EchoLiu-MSFT 14,621 Reputation points

2021-11-12T01:25:48.6+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

2 answers

Your answer

Tom Phillips 17,771 Reputation points

2021-11-10T18:18:37.45+00:00

Are you getting an error message? What exactly is happening?
EchoLiu-MSFT 14,621 Reputation points

2021-11-12T01:25:48.6+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Answer 1

Viorel 122.6K

To insert the results into an empty test table, check the approach that uses 'INTO':

drop table if exists #test

;WITH cte as
(
   . . .
)
SELECT . . .
into #test
FROM cte
ORDER BY Firstpart, [date2], quantity

select * from #test

Naresh y 146 Reputation points

2021-11-10T14:24:26.07+00:00

its all abut all the Union all result sets, need to insert into one target table, i think ur query will insert only the cte query part, wt abut the other 2 union all result sets ,is there any other approach i can write

Viorel 122.6K

To insert the results into an existing table MyTable that already has the required columns (Firstpart, date2 and quantity), try this statement:

; WITH cte as
(
   . . .
)
insert into MyTable( Firstpart, date2, quantity )
SELECT . . .
FROM cte

In your case, it is:

;WITH cte
as(SELECT Firstpart,'0001-01-01' [date2],NULL quantity
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY Firstpart ORDER BY Firstpart) rr
FROM #Sourcetable)t WHERE rr=1
UNION ALL
SELECT * FROM #Output
UNION ALL
SELECT Firstpart,'9999-12-31',NULL
FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY Firstpart ORDER BY Firstpart) rr
FROM #Sourcetable)t WHERE rr=1)
insert into MyTable( Firstpart, date2, quantity )
SELECT Firstpart,CASE WHEN [date2] = '0001-01-01' THEN 'STA'
WHEN [date2] = '9999-12-31' THEN 'FAU' ELSE [date2] END [date], quantity
FROM cte

Naresh y 146 Reputation points

2021-11-10T15:44:13.997+00:00

Thank for your prompt answers ,i already tried this will insert only the last result set values ,but not the hole result set
Thank you very much for your support and answers
Viorel 122.6K Reputation points

2021-11-10T16:17:04.327+00:00

What last result set values are inserted? Maybe #Output was incomplete. How to reproduce the problem?
Tom Phillips 17,771 Reputation points

2021-11-10T19:07:51.11+00:00

The CTE is a UNION ALL query. All data is returned by the CTE. What you describe is not possible with the code provided.

Run the select without the INSERT to see what is being returned.

Answer 2

Hi @Naresh y ,

 DROP TABLE IF EXISTS targettable     
      
 ;WITH cte  
 as(SELECT Firstpart,'0001-01-01' [date2],NULL quantity  
 FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY Firstpart ORDER BY Firstpart) rr  
 FROM #Sourcetable)t WHERE rr=1  
 UNION ALL  
 SELECT * FROM #Output  
 UNION ALL  
 SELECT Firstpart,'9999-12-31',NULL   
 FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY Firstpart ORDER BY Firstpart) rr  
       FROM #Sourcetable)t WHERE rr=1)  
     
 SELECT Firstpart,CASE WHEN [date2] = '0001-01-01' THEN 'STA'  
 WHEN [date2] = '9999-12-31' THEN 'FAU' ELSE [date2] END [date], quantity,[date2]  
 INTO targettable  
 FROM cte  
 ORDER BY Firstpart,[date2], quantity   
  
 SELECT Firstpart,[date],quantity   
 FROM targettable  
 ORDER BY Firstpart,[date2], quantity

Output:

If this is the output you expect. So other people struggling with this problem may be because they don’t know the existence of a sorting column. I added a sorting column to the CTE I wrote earlier, and you can get the output you want based on this sorting column.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards,
Echo

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

How to combine the results of the union all queries ?

2 answers

Your answer