SQL Server Performance

Updates from various data sources...

Discussion in 'SQL Server DTS-Related Questions' started by anglo, Mar 30, 2004.

  1. anglo New Member

    I am using SQL Sever 2000 to try and create a DTS that,

    1 Downloads a MS Access database used by a website
    2 Updates the master SQL Server database with any changes from the web
    3 Repopulates the entire web db
    4 Sends db back to website

    Could somebody please tell me the easiest way of updating existing records in the SQL database??

    If I was to attempt this using just one db then my SQL may look like,

    UPDATE Table1 SET Table1.CONTACT_Name = Table2.CONTACT_Name
    FROM Table1, Table2
    WHERE Table1.CONTACT_ID = Table2.CONTACT_ID

    My problem is that 'Table1' and 'Table2' are in two separate database connections, the web db is MS Access and the master db is SQL Server.... Is there any way of using an UPDATE statement without having to use temporary tables in the SQL db??

    Any help would be appreciated.

  2. satya Moderator

    It would be better approach to use TEMP tables in this case, rather than importing to a table.
    Are you hitting any issues with current approach?


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. anglo New Member

    Thanks satya.

    I can achieve the updates using temporary tables however I was just wondering if there was a more efficient way of updating the master database. The way I am approaching it at the moment is,

    1 Copy any amended rows from the web db to a temporary table in SQL
    2 Update the master tables by linking to records in the temp table

    I will continue to do this for the time being.
  4. satya Moderator

    If you aren't hitting any performance with this approach then leave it, to be precise .... if ain't broke ... then don't fix it. [8D]

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page