Need help for a Query

  • HI,

    We a have 2 tables.I need to insert values form DAT_STAGE table to DAT_DES with out duplicate records.

    I have used below query.

    insert into DAT_DES

    (lvl_nme, ds_nme, ds_size, create_dt, rating_dt)

    (select LTRIM(REPLACE(DAT_STAGE.lvl_nme, 'D', '')), ds_nme, ds_size, create_dt, substring(ds_nme, 3, 8)

    from DATA_STAGE

    where DAT_STAGE.ds_nme + LTRIM(REPLACE(DAT_STAGE.lvl_nme, 'D', '')) NOT in

    (select ds_nme + str(lvl_nme) from DATA_DES))

    But it is inserting duplicate values.can any one help me how to insert values with out inserting the duplicate recored.

    plz help me to modify the above query...

  • Simple question have you checked the DATA_STAGE table for duplicated rows?

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • we have an ssis packa created in such a way that

    Data will loaded into the DAT_STAGE from Flat file and then we need to insert that values from Dat_stage to Dat_DES.

    Daily this SSIS job will run and data will in to the table.

    we have created a step in the JOb in such way From DAT_STAGE values should insert into the DAT_DES with out duplicate values with the above query which mention.

    But it is not working........

    can any one give me an idea....

    Thank you very much for reply......

    Many thanks..........

  • I cant see anything wrong with the query, thats why I asked if there were duplicated records in the staging table.

    If you run

    SELECT

    lvl_nme

    ,ds_nme

    ,count(*)

    From DAT_STAGE

    Group by

    lvl_nme

    ,ds_nme

    Having count(*)>1

    Do you get any duplicates?

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • If using SQL2008 you can also use a MERGE instead of the INSERT...WHERE NOT IN structure. This should be much more robust and considerably faster.

  • There are no duplicate values in the staging table ....

    SSIS job is created in such a way that after inserting the Values to DAT_DES form DAT_STAGE we will delete the data present on the DAT_STAGE table.....

    Next day when the ssis job runs the fresh data will load into the DAT_Stage....from Flat file...

    Some times this data will contain the same values...i.e values that are present on DAT_DES.......

    So we need to insert only those values which does not contain on DAT_DES.....

    Many THanks,

  • Hi Richard Warr,

    Thanks Fo reply,

    Can you please help me how to wright that Query using Merge........

    Many Thanks...............

  • Ok, If you state there are no duplicates in the DAT_STAGE then thats fine, I'm always suspicious of data loaded from files as they can easily have duplicates in them, and neither of the solutions below will prevent duplicates occuring if there are duplicated rows in the source file.

    There are a couple of options, using the merge statement is one,

    MERGE INTO DAT_DES as Target

    USING DAT_STAGE as Source

    ON Target.ds_nme=Source.ds_nme

    and Targer.lvl_nme = LTRIM(REPLACE(Source.lvl_nme, 'D', ''))

    When NOT MATCHED BY TARGET THEN

    INSERT (lvl_nme, ds_nme, ds_size, create_dt, rating_dt)

    VALUES LTRIM(REPLACE(Source.lvl_nme, 'D', '')), Source.ds_nme, Source.ds_size, Source.create_dt, substring(Source.ds_nme, 3, 8));

    The other is to use a NOT EXISTS, Something like

    insert into DAT_DES

    (lvl_nme, ds_nme, ds_size, create_dt, rating_dt)

    SELECT LTRIM(REPLACE(DAT_STAGE.lvl_nme, 'D', '')), ds_nme, ds_size, create_dt, substring(ds_nme, 3, 8)

    from DAT_STAGE s

    WHERE NOT EXISTS

    (SELECT 1

    FROM DAT_DES d

    WHERE d.ds_nme=s.ds_nme

    AND d.lvl_nme=LTRIM(REPLACE(s.lvl_nme, 'D', '')))

    I'm not sure how this will perform as I've seen strange query performances when modifying data in a NOT EXISTS where clause.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • THanks you so much for helping me with the Query...

    we want to delete the duplcate record from a table (DAT_DES--which i mentinon above).

    Can you please help me how to wright the query to delete the duplcate record on the table " DAT_DES"

    using merge.

    Many thanks,

    Vijay.

  • If you using merge solution given by "Jason-299789 " then duplicate data will not going to come

    and if you want to delete duplicate record from same table

    then use simple row_number,or using distinct

    you can delete data

    like

    delete from

    (

    select a,row_number() over (partition by a order by a) as srno,b

    from @temptable

    ) c

    where srno>1

Viewing 10 posts - 1 through 9 (of 9 total)

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