none
Replacing a table in a SQL database with a local table... RRS feed

  • Question

  • I need to be able to replace all the data in a table in a SQL Server DB.  But I want to be able to keep the old values in place until all of the new values are ready to take over.

     

    So there are two methods I thought of:

     

    1) Create a temp table on the SQL Server and add all the records to it.  When the table is populated, either truncate the original and copy the data over with an INSERT, or delete the old table and rename the temp table.

     

    2) Create a table locally in the app and populate it.  I figure this will be much faster than creating the tmep tab le on the SQL server.  But then I need a way of getting the local table to the SQL Server in an efficient manner.

     

    Any thoughts or advice are welcome...

     

    Thanks.

     

    J

    Wednesday, August 29, 2007 5:46 AM

Answers

  • Okay...  So maybe I'm thinking too much...

     

    I suppose, since this is ADO .NET, I can make whatever changes I want to the table on my side and if I don't want to commit them, I just don't call the update method...  Right?

     

    J

     

    Wednesday, August 29, 2007 6:26 AM
  • Absolutely. Remember though, you need to implement the correct InsertCommand, UpdateCommand and DeleteCommand for the SqlDataAdapter so it can execute the correct command when updating the database.

    So yes, if you made changes locally and you wish to disregard them, just don't call the Update() command.

    SqlDataAdapter will fill a dataset/datatable with records based on your Select command and is an in memory disconnected representation of the records on the database

     

    Wednesday, August 29, 2007 11:52 AM

All replies

  • Okay...  So maybe I'm thinking too much...

     

    I suppose, since this is ADO .NET, I can make whatever changes I want to the table on my side and if I don't want to commit them, I just don't call the update method...  Right?

     

    J

     

    Wednesday, August 29, 2007 6:26 AM
  • Absolutely. Remember though, you need to implement the correct InsertCommand, UpdateCommand and DeleteCommand for the SqlDataAdapter so it can execute the correct command when updating the database.

    So yes, if you made changes locally and you wish to disregard them, just don't call the Update() command.

    SqlDataAdapter will fill a dataset/datatable with records based on your Select command and is an in memory disconnected representation of the records on the database

     

    Wednesday, August 29, 2007 11:52 AM