Adding records to a SQL table

I delete all records in a table and add records to it.  When I run a second SQL script to add more records they aren't added to the end.  There isn't any index and no sequence to the data.
PolyfilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

micropc1Commented:
Does your table have a primary key? If you don't specify an ORDER BY clause in your SELECT statement, SQL Server will order the results by the primary key.
0
PolyfilAuthor Commented:
There aren't any keys assigned to this table.
0
Kevin CrossChief Technology OfficerCommented:
What are the actual scripts involved here? Do you get any errors or messages, during execution of the second script OR does it finish successful just without proper insertion?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PolyfilAuthor Commented:
I don't get any errors.  It finishes successfully and the number of records are correct.
0
PolyfilAuthor Commented:
Here is the code:
                                                                  Delete From MikeDB.dbo.MDConsolidate$

                                                                  INSERT INTO MikeDB.dbo.MDConsolidate$ (InvTid, Descr, SiteId, QtyOnHand, QtyAvail, QtyPrd, QtyAdj, Sales, Fcst, POOpen, POInv, OvInv, POExc, OvExc, Need)

                                                                  Select

                                                                  C.InvTid,
                                                                  C.Descr,
                                                                  C.SiteId,
                                                                  C.QtyOnHand,
                                                                  C.QtyAvail,
                                                                  C.QtyPrd,
                                                                  C.QtyAdj,
                                                                  C.Sales,
                                                                  C.Fcst,
                                                                  C.POOpen,
                                                                  C.POInv,
                                                                  0,0,0,0

                                                                  From ##ConInv C                        

select * from MikeDB.dbo.MDConsolidate$
                                                                  --      Load Summary

print 'load summary'
                                                                  INSERT INTO MikeDB.dbo.MDConsolidate$ (InvTid, Descr, SiteId, QtyOnHand, QtyAvail, QtyPrd, QtyAdj, Sales, Fcst, POOpen, POInv, OvInv, POExc, OvExc, Need)

                                                                  Select

                                                                  C.InvTid,
                                                                  I.Descr,
                                                                  'Total',
                                                                  C.QtyOnHand,
                                                                  C.QtyAvail,
                                                                  C.QtyPrd,
                                                                  C.QtyAdj,
                                                                  C.Sales,
                                                                  C.Fcst,
                                                                  C.POOpen,
                                                                  C.POInv,
                                                                  0,0,0,0

                                                                  From ##ConInvSum C
                                                                  INNER JOIN Inventory I on I.InvtID = C.InvTid      
                                                                  
                                                                  --  Calculate totals
print 'update calc'                                                                  
                                                                  UPDATE MikeDB.dbo.MDConsolidate$  SET
                                                                  
                                                                  
                                                                  OvInv =                                                            
                                                                   CASE
                                                                        When (QtyOnHand > Fcst)      Then QtyOnHand - Fcst      Else 0 END,
                                                                        
                                                                  POExc =                                                            
                                                                   CASE
                                                                        When (POOpen > Fcst) Then POOpen - Fcst      Else 0 END,

                                                                  OvExc =                                                            
                                                                   CASE
                                                                        When (POInv > Fcst)      Then POInv - Fcst Else 0 END,

                                                                  Need =                                                            
                                                                   CASE
                                                                        When (POInv < Fcst)      Then Fcst - POInv Else 0 END
                                                                        
                                                                  From MikeDB.dbo.MDConsolidate$
                                                                  Where SiteId = 'Total'
0
Anthony PerkinsCommented:
Unless you use a ORDER BY clause there is no guarantee how the data is returned.  Regardless of whether you have defined  PRIMARY KEY or not.
0
micropc1Commented:
A primary key or any clustered index will determine how the rows are physically ordered in a table. If no clustered index is defined then the data is considered to be in a "heap" and is returned at random - in which case an ORDER BY clause should be used to guarantee consistent results.

http://www.mssqltips.com/sqlservertip/1254/clustered-tables-vs-heap-tables/

One simple solution might be to add a new primary key identity column to the table.

Something like...

ALTER TABLE MikeDB.dbo.MDConsolidate$ ADD
COLUMN idCol INT IDENTITY
CONSTRAINT idColPK PRIMARY KEY
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>A primary key or any clustered index will determine how the rows are physically ordered in a table.<<
First of all and contrary to popular belief, a primary key is a constraint and not an index.  In the speciifc case of SQL Server a Primary Key is enforced with a unique index which does not have to be clustered.  But all of this is irrelevant when it comes to returning data.  So once again let me restate it:
There is no guarantee how the data is returned unless an ORDER BY clause is used.
0
Kevin CrossChief Technology OfficerCommented:
I 100% agree with Anthony. If you are not getting errors and the records insert properly but you require chronological integrity, then I would add a DATETIME column to table with a default of CURRENT_TIMESTAMP.

When you select from the table, you can use:
ORDER BY the_date_column

This will place the newest records on the bottom, which was your original question.

An auto-increment primary key does the same thing if you use ORDER BY pk; however, from a logical standpoint if one is concerned with order of insertion, I would think a date would be more valuable down the line as you can see the rows added today, yesterday, a week ago, etc versus a sequence of ids that are not time-bound with regard to insertion.
0
micropc1Commented:
Yes acperkins, you are correct that the primary key is not an index - it is certainly a constraint; however, creating a primary key constraint will create a clustered index automatically if no clustered index already exists and a nonclustered index isn't specified. So the above example would create a primary key with a clustered index, which would sort the table by that key.

I do agree that you should always use an ORDER BY clause regardless - if for no other reason than that it makes your intended result more obvious.

http://msdn.microsoft.com/en-us/library/ms186342.aspx
0
Anthony PerkinsCommented:
I do agree that you should always use an ORDER BY clause regardless - if for no other reason than that it makes your intended result more obvious.
No, that is not what I said.  Using an ORDER BY clause is the only way to guarantee the result.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.