SQL Insert with Union All

Larry Brister
Larry Brister used Ask the Experts™
on
My attached code works fine with one annoying exception

The data is not going into the table in the same order that my union all's are written.

No matter what I do...it is inserted with an ascii sort on the afsSourceDetail column

So...
Instead of it going in like this...which I absolutely need
P01-REG
P01-FED
P01-St
P01-CITY


It goes in like this.
P01-CITY
P01-FED
P01-REG
P01-ST

Any suggestions?
ALTER PROCEDURE [dbo].[sp_cfa_AfsSplitLT_Taxes](@afsid int )  
AS 
--Get afsSource for return
Begin
	Declare @afsSource nvarchar(20)
	Set @afsSource = (Select afsSource from proc_cfa.dbo.P_Availableforsale where afsid = @afsid)
End

--Add taxes to Available for sale
Begin 
	--Get Summary Data  
   ;WITH taxesCTE(P_Fed, P_St, P_City, P_R1, afsAmount, afsSource, afsSourceDetail, afsDate, afsStatus, afsTransaction) 
   --from 
	AS (  Select round(IsNull(p.fedW2G,0) / 100 * x.afsAmount,2) [P_Fed],   
				 round(IsNull(p.stateW2G,0) / 100 * x.afsAmount,2) [P_St],  
				 round(IsNull(p.cityW2G,0) / 100 * x.afsAmount,2) [P_City],                 
				x.afsAmount	- (round(IsNull(p.fedW2G,0) / 100 * x.afsAmount,2))
							- (round(IsNull(p.stateW2G,0) / 100 * x.afsAmount,2)) 
							- (round(IsNull(p.cityW2G,0) / 100 * x.afsAmount,2)) [P_R1],  
				 x.afsAmount,  
				 x.afsSource,  
				 x.afsSourceDetail,  
				 x.afsDate,  
				 x.afsStatus,  
				 x.afsTransaction  
		  From proc_lt.dbo.p_deals p  
				Join	(Select top 1 SUBSTRING(afsSource,4,4) DealID, * 
						from proc_cfa.dbo.P_AvailableForSale where afsid = @afsid
						order by afsSource) x  
				on p.DealID = x.DealID  
	   ) 
   
   --use CTE Data and Insert all rows with one insert/ where each insert afsamount > 0 
	INSERT INTO PROC_CFA.dbo.P_AvailableForSale  
						   (afsSource  
						   ,afsSourceDetail  
						   ,afsDate  
						   ,afsAmount  
						   ,afsStatus  
						   ,afsTransaction) 
	SELECT afsSource, afsSourceDetail+ '-REG', afsDate, P_R1, afsStatus, afsTransaction  
	FROM taxesCTE  where P_R1 > 0
	UNION
	SELECT afsSource, afsSourceDetail+ '-FED', afsDate, P_Fed, afsStatus, afsTransaction  
	FROM taxesCTE where P_Fed > 0 
	UNION
	SELECT afsSource, afsSourceDetail+ '-ST', afsDate, P_St, afsStatus, afsTransaction  
	FROM taxesCTE  where P_St > 0  
	UNION 
	SELECT afsSource, afsSourceDetail+ '-CITY', afsDate, P_City, afsStatus, afsTransaction  
	FROM taxesCTE where P_City > 0 
    --Remove original afsid   
	Delete from proc_cfa.dbo.P_AvailableForSale where afsid = @afsid  
 
End 

Select @afsSource as afsSource

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Software Engineer
Commented:

Simple UNION looks for duplicate records

Try using UNION ALL instead.
............
        SELECT afsSource, afsSourceDetail+ '-REG', afsDate, P_R1, afsStatus, afsTransaction  
      FROM taxesCTE  where P_R1 > 0
      UNION ALL
      SELECT afsSource, afsSourceDetail+ '-FED', afsDate, P_Fed, afsStatus, afsTransaction  
      FROM taxesCTE where P_Fed > 0
      UNION ALL
      SELECT afsSource, afsSourceDetail+ '-ST', afsDate, P_St, afsStatus, afsTransaction  
      FROM taxesCTE  where P_St > 0  
      UNION ALL
      SELECT afsSource, afsSourceDetail+ '-CITY', afsDate, P_City, afsStatus, afsTransaction  
      FROM taxesCTE where P_City > 0
Ephraim WangoyaSoftware Engineer

Commented:
Or you can assign a value and use it for sorting

      SELECT 1 ROWNO, afsSource, afsSourceDetail+ '-REG', afsDate, P_R1, afsStatus, afsTransaction  
      FROM taxesCTE  where P_R1 > 0
      UNION ALL
      SELECT 2 ROWNO, afsSource, afsSourceDetail+ '-FED', afsDate, P_Fed, afsStatus, afsTransaction  
      FROM taxesCTE where P_Fed > 0
      UNION ALL
      SELECT 3 ROWNO, afsSource, afsSourceDetail+ '-ST', afsDate, P_St, afsStatus, afsTransaction  
      FROM taxesCTE  where P_St > 0  
      UNION ALL
      SELECT 4 ROWNO, afsSource, afsSourceDetail+ '-CITY', afsDate, P_City, afsStatus, afsTransaction  
      FROM taxesCTE where P_City > 0
8080_Diver

Commented:
Why does it matter?  When you access the data, you can order it bey whatever you please.  (You might want to try executing the SELECT statement in a query tool and looking at the order in which the data appears . . . it just may match the rder that the data is being put into the table. ;-)  On the other hand, have you tried adding an ORDER BY to force the issue?

You do realize that using UNION does not mean Union ALL, right?  UNION will cause the data to be sorted so that duplicates can be identified and removed.
Larry Bristersr. Developer

Author

Commented:
Excellent...thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial