Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Data transfer while dynamic increment of duplicate values. Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 9:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:33 AM
Points: 19, Visits: 45
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.
Post #1449039
Posted Friday, May 3, 2013 2:04 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 10:34 AM
Points: 826, Visits: 1,177
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
Post #1449354
Posted Monday, May 6, 2013 3:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:33 AM
Points: 19, Visits: 45
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


  Post Attachments 
Duplicate ROs insertion issue.xlsx (22 views, 13.21 KB)
Post #1449638
Posted Monday, May 6, 2013 4:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 10:34 AM
Points: 826, Visits: 1,177
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.

Post #1449652
Posted Monday, May 6, 2013 5:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:33 AM
Points: 19, Visits: 45
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.
Post #1449658
Posted Monday, May 6, 2013 7:42 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 10:34 AM
Points: 826, Visits: 1,177
Change name of CTE to something else other than DTAGatHERTZ_RO.
Post #1449719
Posted Tuesday, May 7, 2013 6:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 10:34 AM
Points: 826, Visits: 1,177
Have you tried it?
Post #1450103
Posted Tuesday, May 7, 2013 12:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:33 AM
Points: 19, Visits: 45
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.
Post #1450299
Posted Tuesday, May 7, 2013 1:00 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:33 AM
Points: 1,912, Visits: 19,442
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
Post #1450303
Posted Tuesday, May 7, 2013 1:07 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 10:34 AM
Points: 826, Visits: 1,177
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.

Post #1450304
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse