Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2002
    Location
    Houston Texas
    Posts
    2

    Angry Unanswered: Updating a SQL 2000 Table

    I have been attempting to update a table in SQL 2000, this is in a database of student information where we get periodical updates on status changes, or course additions or drops. My problem lies in that I can append the table adding the totally new records but it adds a duplicate row of information if the record exists and needs to be upgraded. This is all new to me and any advice would be appreciated.

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    This new data that is given to you, is it in a file or is the data being updated by a frontend application?

  3. #3
    Join Date
    Feb 2002
    Location
    Houston Texas
    Posts
    2
    The data to update with is in a csv type file

  4. #4
    Join Date
    Feb 2002
    Posts
    4
    I think that you may provide us more details about the structure of the table/tables.
    Usually if you want to prevent record duplication you have to define in the DB how to identify each record ...

  5. #5
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    If you are looping through the file one record at a time then:

    Check to see if the record exists by matching on Primary Key, if yes then UPDATE else INSERT.


    IF EXISTS (SELECT * FROM tbl WHERE tblKey = CSVKey)
    UPDATE tbl
    ELSE
    INSERT tbl

    If you do it in bulk, load CSV into a "Load" table, then UPDATE the master table from the "Load" where keys match, then INSERT into the master table where NOT EXISTS a match from the "Load" table


    BULK INSERT .... csv

    UPDATE m
    SET .....
    FROM master m, Load l
    WHERE m.tblKey = l.csvKey

    INSERT master ......
    SELECT .......
    FROM Load l
    WHERE NOT EXISTS (
    SELECT *
    FROM master m
    WHERE m.tblKey = l.csvKey
    )

  6. #6
    Join Date
    Mar 2002
    Posts
    7

    Thanks!

    Thanks for the information! I were having the same problem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •