Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

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.
0
Polyfil
Asked:
Polyfil
  • 3
  • 3
  • 3
  • +1
1 Solution
 
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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
 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now