Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data transfer while dynamic increment of duplicate values.


Data transfer while dynamic increment of duplicate values.

Author
Message
Prasune Verma
Prasune Verma
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 74
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.
Neeraj Dwivedi
Neeraj Dwivedi
SSC Eights!
SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)

Group: General Forum Members
Points: 887 Visits: 1319
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
Prasune Verma
Prasune Verma
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 74
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
Attachments
Duplicate ROs insertion issue.xlsx (22 views, 13.00 KB)
Neeraj Dwivedi
Neeraj Dwivedi
SSC Eights!
SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)

Group: General Forum Members
Points: 887 Visits: 1319
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.
Prasune Verma
Prasune Verma
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 74
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.
Neeraj Dwivedi
Neeraj Dwivedi
SSC Eights!
SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)

Group: General Forum Members
Points: 887 Visits: 1319
Change name of CTE to something else other than DTAGatHERTZ_RO.
Neeraj Dwivedi
Neeraj Dwivedi
SSC Eights!
SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)

Group: General Forum Members
Points: 887 Visits: 1319
Have you tried it?
Prasune Verma
Prasune Verma
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 74
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.
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33041
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

Neeraj Dwivedi
Neeraj Dwivedi
SSC Eights!
SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)

Group: General Forum Members
Points: 887 Visits: 1319
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search