Data transfer while dynamic increment of duplicate values.

Question

Data transfer while dynamic increment of duplicate values.

Prasune Verma

SSC-Addicted

Points: 453
More actions
May 2, 2013 at 9:41 pm

#394713

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.

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

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

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 1

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 SSC-Addicted Points: 453 More actions · Answer 2

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

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 3

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 SSC-Addicted Points: 453 More actions · Answer 4

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 SSCertifiable Points: 6768 More actions · Answer 5

Change name of CTE to something else other than DTAGatHERTZ_RO.

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 6

Neeraj Dwivedi

SSCertifiable

Points: 6768

May 7, 2013 at 6:47 am

#1612952

Have you tried it?

Prasune Verma SSC-Addicted Points: 453 More actions · Answer 7

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 SSC Guru Points: 51550 More actions · Answer 8

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

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 SSCertifiable Points: 6768 More actions · Answer 9

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.

Lowell SSC Guru Points: 323518 More actions · Answer 10

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!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

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';

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 12

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

Lowell SSC Guru Points: 323518 More actions · Answer 13

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!

Prasune Verma SSC-Addicted Points: 453 More actions · Answer 14

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.