none
DATA UPDATION METHOD RRS feed

  • Question

  • Dear Friends,

    I got an argument when I was discussing about updating data into SQL Server with my colleague.

    He suggests me to

    1. First, Delete the data.
    2. then Insert the new (updated in front end) data.

    But I was arguing with him that 'using UPDATE query' will save time rather to use 'DELETE and INSERT'.

    Also please justify this in relationship from c#.net 2.0.

    Please give us a conclusion.

    Thanks in advance.

    Chandramohan Palaniswamy.
    Friday, September 26, 2008 7:55 AM

Answers

  • in C# there is an update method defined for the RecordSet class in ADO.  You can use that to perform an update if that is what you are asking.  Of course update is safer and faster than delete + insert (especially for multiple rows!).

    Here is an example of where delete + insert would cause serious problems:

    table theoretical layout stripped of SQL types

    int customerID      string FirstName    string    LastName
    1       jim     jones
    2       jane   jonesz
    3       jane   jonesz
    4       billy   bobkin


    Now if we had a scenario where someone in data entry claimed that they mistyped "jones" as "jonesz" for a whole day, we might want to go back and fix that... How does this work with delete + insert?  Well, we have to find all the data, delete using the customer ID as a primary key (as long as it would suffice) and then insert for each jonesz.  All the while, we are transferring data from the server to the client to do the work.  Now if we just issue an UPDATE command, it occurs strictly on the server and there is no need to load the network.

    Hope that helps,

    John
    Friday, September 26, 2008 11:02 PM

All replies

  •  

    Yes you are right, update query will be faster and a better approach.

    See the scenario when you want to change the value of single cell, it will be stupid to delete all the records in the table just to change one value.

    Can't comment about C#.net.

    Friday, September 26, 2008 8:40 AM
  • in C# there is an update method defined for the RecordSet class in ADO.  You can use that to perform an update if that is what you are asking.  Of course update is safer and faster than delete + insert (especially for multiple rows!).

    Here is an example of where delete + insert would cause serious problems:

    table theoretical layout stripped of SQL types

    int customerID      string FirstName    string    LastName
    1       jim     jones
    2       jane   jonesz
    3       jane   jonesz
    4       billy   bobkin


    Now if we had a scenario where someone in data entry claimed that they mistyped "jones" as "jonesz" for a whole day, we might want to go back and fix that... How does this work with delete + insert?  Well, we have to find all the data, delete using the customer ID as a primary key (as long as it would suffice) and then insert for each jonesz.  All the while, we are transferring data from the server to the client to do the work.  Now if we just issue an UPDATE command, it occurs strictly on the server and there is no need to load the network.

    Hope that helps,

    John
    Friday, September 26, 2008 11:02 PM