?
Solved

Insert Order doesn't make sense

Posted on 2011-05-10
6
Medium Priority
?
286 Views
Last Modified: 2012-05-11
My attached code is working...but strangely.

The top line being inserted
SELECT afsSource, afsSourceDetail+ '-Reg', afsDate, P_R1, afsStatus, afsTransaction  
      FROM taxesCTE  where P_R1 > 0

is ACTUALLY going in second.

Any suggestions?
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

Open in new window

0
Comment
Question by:lrbrister
  • 3
  • 3
6 Comments
 
LVL 15

Expert Comment

by:tim_cs
ID: 35730413
How do you know it's going in second?  Is there a unique primary key in P_AvailableForSale and that record always has a key after the 2nd SELECT record?  Or are you just looking at how the data is ordered in the table after it's inserted?  
0
 

Author Comment

by:lrbrister
ID: 35730458
tim_cs:
There is a UID in P_AvailableForSale

When I run and then look at the inserted data, the top select is going in second

You can see in my data below...that the city taxes didn;t go in because it wasn't > 0
And the ALL-Reg went in second.

The first column is the uid
36648	LT-8016-20171113	ALL-Fed	2017-11-13 00:00:00	12500.00	1	0
36649	LT-8016-20171113	ALL-Reg	2017-11-13 00:00:00	35000.00	1	0
36650	LT-8016-20171113	ALL-St	2017-11-13 00:00:00	2500.00	1	0

Open in new window

0
 
LVL 15

Accepted Solution

by:
tim_cs earned 2000 total points
ID: 35730490
Try using "union all" instead of just union.  
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Closing Comment

by:lrbrister
ID: 35730510
tim_cs:
That was it.

Can't believe I didn't try that first
0
 
LVL 15

Expert Comment

by:tim_cs
ID: 35730516
As to why that makes a difference.  UNION only returns unique records so it's ordering the selects to eliminate duplicates.  UNION ALL returns all rows no matter what so it doesn't have to reorder them.  
0
 

Author Comment

by:lrbrister
ID: 35730809
tim_cs:,
  Really good information.  been using both for years and never really put that together.  
I'm actually a VB.NET web developer and usually just dealing with selects, inserts etc...
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question