Solved

Adding records to a SQL table

Posted on 2012-03-21
11
317 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 60

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

695 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