Solved

Adding records to a SQL table

Posted on 2012-03-21
11
315 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 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