Update the duplicate records in SQLserver

  • Hi,

    i am loading data from text file to the db using a dts package.it involves follwing stages

    1)loading data to staging table from textfile

    2)matching the records in the staging table with the existing records and updating the staging table "consumerid" column with the consumerid in the Db

    3)updating the records in the oroginal table from staging table if consumerID has a value in the staging table after the matching process is done

    4)finally inserting all the records into the original table whose consumerids in the staging table are Null

    the table structure of the staging table is as follows.

    StagingId |ConsumerId |firstname |secondname| nationalIDno |Accountno | address

    1 | |a | b | 123456 |12345089 |abcabc

    2 | |a | b | 123456 |36780900 |abcabc

    i have two original tables one for storing personal info and other for storing Account info

    orignal tables structure should be as follows

    ConsuemrId firstname secondname nationalIDno

    1 a b 123456

    ConsumerID AccountNo

    1 12345089

    1 36780900

    from the above tables u can observer the same consumer has two different accounts.

    Now the problem is if i am inserting the record for the first time in the database which means i check for the record existance in the original table and the record does not exist which means the "consumerId" in the staging table has null values.and it is inserting the data as two different records and creating two consumerIds how do avoid this duplication problem.

    thanks in advance.

  • INSERT INTO PersonalInfo (firstname, secondname, nationalDno)

    SELECT DISTINCT firstname, secondname, nationalDno

    FROM StagingTable S

    WHERE NOT EXISTS

    (

    SELECT *

    FROM PersonalInfo P

    WHERE S.firstname = P.fistname

    AND S.secondname = P.secondname

    AND S.nationalDno = P.nationalDno

    )

  • hi ken,

    Thanks for the reply.It worked for me.

  • delete from

    tableA

    where pid not in

    ( select pid, CN, mk, te, yr, st, cr, cc from

    tableA where st = 812 and te = 1 and YR = 11

    group by PID, cn, mk, te, yr, st, CR, cc

    having count (*) > 1 )

    I am trying to run this query but geting this error message:

    Msg 116, Level 16, State 1, Line 7

    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

    CAn somebody help. Thank you so much.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply