Data transfer while dynamic increment of duplicate values.

  • Hi,

    I have to insert data from one table to another in SQL 2005 (destination having lesser but the same name of columns as of the Source table). The primary key combination for the destination table is "ROBumber + ROTime" . But the source table has many duplicate entries for the same "RONumber + ROTime" combination. Its primary key is different.

    What I have to do is that while inserting, whenever the RONumber is duplicate, I have to increment the ROTime by 1 minute, such that the primary key condition is intact and data is successfully inserted into the destination table.

    Please help me whichever way is possible, either to write an SP for this or to write an SSIS. But I am unable to think how to do the same.

    Thanks, in advance.

  • Something like this will work. You have to validate this in your environment. That's whay people ask you to post code in case you need quick answers.

    WITH DupCTE

    (ROBumber,ROTime,Ranking)AS

    (SELECT ROBumber,ROTime, Ranking = DENSE_RANK()

    OVER(PARTITION BY ROBumber,ROTime

    ORDER BY NEWID() ASC)

    FROM DupValues s

    )

    INSERT INTO SinValues

    SELECT ROBumber,

    CASE WHEN Ranking = 1 THEN ROTime ELSE DATEADD(mi,Ranking, ROTime)

    END

    FROM DupCTE

  • Thanks for your reply Neeraj and my apologies for the delayed response due to the Weekend.

    I tried your solution but unfortunately it gave me some errors.

    I have attached the sample data from the source table and also the details of both the tables. The structure of both the tables are same except that the source table does not have a primary key and the destination table has a defined primary key combination.

    I have mentioned required details in the attached sheet with the name "Duplicate ROs insertion issue".

    Please let me know if you need more info.

    Thank You in advance.

    Thanks and Regards,

    Prasune Verma

    +91 9503194429

  • Tell me what error you got? Also I need data in the form of script where I can insert that in my environment without writing the code.

  • This is the error that I got:-

    "Recursive common table expression 'DTAGatHERTZ_RO' does not contain a top level Union All operator."

    I had tried this:

    WITH DTAGatHERTZ_RO

    (RO,Time_In,Ranking)AS

    (SELECT RO,Time_In, Ranking = DENSE_RANK() OVER (PARTITION BY RO,Time_In ORDER BY NEWID() ASC)

    FROM DTAGatHERTZ_RO )

    INSERT INTO ROs

    SELECT Class, Date_Entered, PMmiles, RO, RoDate, CASE WHEN Ranking = 1 THEN Time_In ELSE DATEADD(mi,Ranking, Time_In) END

    from DTAGatHERTZ_RO where RO = '4187'

    Please refer to the excel sheet that I had provided for more details on data. Please let me know if you need more info on this.

  • Change name of CTE to something else other than DTAGatHERTZ_RO.

  • Have you tried it?

  • I am sorry Neeraj, but unfortunately that does not work.

    Ok, can u please give me a query to just increment the 'Time_In' value by 1 minute whenever the 'RO' value is duplicate. If that gets done, I will have distinct "RO + Time_In" combinations which is primary key for the destination table.

    So in that way, instead of incrementing the Time_In values while inserting, I can update the Time_In values in incremental way first and then can easily run the insert query.

    Please use the same sheet with name "Duplicate ROs insertion issue" for reference which I had provided.

    Thanks a lot for your patience on this.

  • can you please do as asked

    Neeraj Dwivedi (5/6/2013)


    Also I need data in the form of script where I can insert that in my environment without writing the code.

    the times in your spreadsheet seem a bit "odd".

    Time_In

    1899-12-30 07:47:00.000

    1899-12-30 10:32:00.000

    1753-01-01 12:01:00.000

    1753-01-01 12:01:00.000

    1899-12-30 13:52:00.000

    ......if you provide some create table scripts and data insert scripts that can be used in SSMS, then I am sure that you will get a fast and tested answer.

    I don't think that to keep referring to a spreadsheet is going to help.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • I was able to replicate exact same issue in my environment if the CTE name & name of table is same, so I do not see any reason that it will not work.

    Are you getting same error or some other error?

    Your code is not working because you have selected only 3 columns in CTE but while inserting you are also asking for Class, Date_Entered, PMmiles which CTE does not have.

  • i f he's inserting into a constraint, and one of the new generated values happens to be the same as an existing value, i'd expect an error;

    for example, just based on the RO time, data like the item exemplified below would generate two rows withtr he smae time, which might violate the constraint.

    2013-05-07 15:09:00.00

    2013-05-07 15:09:00.00 <-- this would generate 2013-05-07 15:10:00.00, a duplicate value

    2013-05-07 15:10:00.00

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • With nothing to do any testing with, give this a try:

    WITH BaseData(

    Class,

    Date_Entered,

    PMmiles,

    RO,

    RoDate,

    Time_In,

    Ranking

    )AS(

    SELECT --RO,Time_In, Ranking = DENSE_RANK() OVER (PARTITION BY RO,Time_In ORDER BY NEWID() ASC)

    Class,

    Date_Entered,

    PMmiles,

    RO,

    RoDate,

    Time_In,

    Ranking = row_number() over (partition by RO, RoDate, Time_In order by (select null))

    FROM

    DTAGatHERTZ_RO

    )

    INSERT INTO ROs

    SELECT

    Class,

    Date_Entered,

    PMmiles,

    RO,

    RoDate,

    DATEADD(mi,Ranking - 1, Time_In

    from

    BaseData

    where

    RO = '4187';

  • Lowell, he has mentioned in first post that The primary key combination for the destination table is "ROBumber + ROTime" .

  • Neeraj Dwivedi (5/7/2013)


    Lowell, he has mentioned in first post that The primary key combination for the destination table is "ROBumber + ROTime" .

    agreed, but if he auto generates an overlapping value ,"ROBumber + ROTime" + 1 in the case of duplicate "ROBumber + ROTime" from the source, he of course could duplicate the value, but against a different row than was incremented.

    that was my point...he's not guaranteeing a PK value is unique with the current logic of arbitrarily incrementing a value.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks Neeraj, It was my lack of pre-knowledge on CTE which was preventing me to interpret your suggested solution correctly. I googled about CTE and then came back to apply what you were trying to suggest and it has worked perfectly.

    Many thanks for your valuable time and solutions. Thank you all for the suggestons. Very much appreciative and supportive.

    Thanks and Regards,

    Prasune Verma.

Viewing 15 posts - 1 through 14 (of 14 total)

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