Solved

Adding records to a SQL table

Posted on 2012-03-21
11
309 Views
Last Modified: 2012-03-23
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
Comment
Question by:Polyfil
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 7

Expert Comment

by:micropc1
ID: 37748855
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
 

Author Comment

by:Polyfil
ID: 37748864
There aren't any keys assigned to this table.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 37748958
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
 

Author Comment

by:Polyfil
ID: 37748969
I don't get any errors.  It finishes successfully and the number of records are correct.
0
 

Author Comment

by:Polyfil
ID: 37748974
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37750550
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
 
LVL 7

Accepted Solution

by:
micropc1 earned 250 total points
ID: 37750913
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37755598
>>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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 37755715
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
 
LVL 7

Expert Comment

by:micropc1
ID: 37755799
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37759770
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now