duplicate records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

duplicate records

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-01-26 : 09:44:23

Dear All,
I am having an issue with duplicate records please help to select duplicate records in the below data table.

invoice seq
40636253 1
40636253 2
40636253 3
40636253 4
40636253 5
40636253 6
40636257 1
40636257 2
40636257 3

Here i need to find the duplicate,

I am trying to insert the data to destination table having composite primary key on these column(invoice, and seq)
It it is throwing primary key error can't insert duplicate key

The table having 800000 records please help .

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-26 : 09:57:50

Are these the only 2 fields you are going to be querying off of? Also are there any chances of getting the same composite key?

If this is your data set then

Select
--Distinct --If you can have 2 identical entries
Invoice
, Max(Seq)
From
t1

EDIT: This method returns unique invoices with their last sequence.

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 10:13:41

Or did you mean this?


SELECT invoice, seq
FROM MyTable
GROUP BY invoice, seq
HAVING COUNT(*) > 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 10:57:40

[code]INSERT INTO Dest(invoice ,seq )
SELECT invoice ,seq
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY invoice ,seq ORDER BY seq) AS RowNo,invoice ,seq
FROM Table
)t
WHERE RowNo=1
[/code]

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 11:00:18

I'll let you do the CROSS APPLY version too then

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-26 : 11:06:04

quote:
Originally posted by gangadhara.ms

I am trying to insert the data to destination table having composite primary key on these column(invoice, and seq)
It it is throwing primary key error can't insert duplicate key

The table having 800000 records please help .

Are you inserting a single record, or multiple. for duplicates, do you want to update the destination or ignore?

You NEED to supply more details

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-01-26 : 11:54:41

Dear All,
Thanks for reply.
Along with above column i have other multiple columns as well. I am trying to insert these values to destination table with having a composite primary key on invoice and seq.
there are 80000 records are there in source
I am doing this in SSIS package ,it through an error saying can't insert the data to destination table becous duplicate key.

annot insert duplicate key row in object 'dbo.FACT_SERVICE_REJECTION' with unique index 'PK_FACT_SERVICE_REJECTION'.

Plase help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 11:57:32

quote:
Originally posted by gangadhara.ms

Dear All,
Thanks for reply.
Along with above column i have other multiple columns as well. I am trying to insert these values to destination table with having a composite primary key on invoice and seq.
there are 80000 records are there in source
I am doing this in SSIS package ,it through an error saying can't insert the data to destination table becous duplicate key.

annot insert duplicate key row in object 'dbo.FACT_SERVICE_REJECTION' with unique index 'PK_FACT_SERVICE_REJECTION'.

Plase help me

did you try solution provided? what happened then?

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-01-26 : 12:07:26

Yes Visakh,i tried but the query is not returning the duplicate keys
its returning even non duplicate data as well..
invoice seq
3123 1
3123 2
3123 3
4555 1
456 1
456 2

my data is like this ..along with soem other column .
In the destination table there is a composite primary key on invoice and seq

Pls help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 12:13:54

quote:
Originally posted by gangadhara.ms

Yes Visakh,i tried but the query is not returning the duplicate keys
its returning even non duplicate data as well..
invoice seq
3123 1
3123 2
3123 3
4555 1
456 1
456 2

my data is like this ..along with soem other column .
In the destination table there is a composite primary key on invoice and seq

Pls help

so you mean you need only duplicate ones? i thought question was to avoid duplicates for insertion to dest table

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-01-26 : 12:21:40

Yes exactly..i need which records are duplicates so that i can eliminate those records in the source and i can load remaining to destination.

Please help

Thanks for all your help..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 12:25:03

quote:
Originally posted by gangadhara.ms

Yes exactly..i need which records are duplicates so that i can eliminate those records in the source and i can load remaining to destination.

Please help

Thanks for all your help..

what i had given initially was way to directly insert only one instance of each group to dest table, which will avoid the pk violation error, however if your reqmnt is to avoid all instances of repeated ones then tweak it like below


INSERT INTO Dest(invoice ,seq )
SELECT invoice ,seq
FROM
(
SELECT COUNT(1) OVER (PARTITION BY invoice ,seq) AS Occur,invoice ,seq
FROM Table
)t
WHERE Occur=1

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-01-26 : 13:06:52

Dear All,

I am using this query in the final SSIS package
i am not able to load the data due to duplicate rows.
Pleas help to get rid of this

INSERT INTO FACT_SERVICE_REJECTION
( GROUP_NUMBER
,INVOICE_NUMBER
,TRANSACTION_SEQ
,PROVIDER_SK
,SECONDARY_PROVIDER_ID
,PATIENT_ID
,REFERRING_PHYSICIAN_ID
,REFERRING_PHYSICIAN_NAME
,ORIGINAL_FSC
,REGISTRATION_FSC
,INVOICE_FSC
,CERT_NUMBER
,INVOICE_CREATION_DATE
,INVOICE_CREATION_PERIOD
,LOCATION
,TES_EXTRACTION_DATE
,TES_RECEIVED_DATE
,INVOICE_BALANCE
,SERVICE_DATE
,POSTING_PERIOD
,POSTING_PERIOD_DATE
,POSTING_DATE
,ID_1
,PROCEDURE_COUNT_ACT
,PROCEDURE_COUNT_MOD
,MODIFIER
,CURRENT_YEAR_RVU_SOURCE
,SERVICE_YEAR_RVU_SOURCE
,CHARGES
,START_TIME
,UNITS_BASE
,UNITS_ADDITIONAL
,UNITS_DURATION
,UNITS_TIME
,UNITS_TOTAL
,TOTAL_TRANSACTION_PAYMENTS
,TOTAL_TRANSACTION_PAYMENTS_PRIMARY
,TOTAL_TRANSACTION_PAYMENTS_SECONDARY
,TRANSACTION_CONT_ADJ
,TRANSACTION_CONT_ADJ_PRIMARY
,TRANSACTION_CONT_ADJ_SECONDARY
,TRANSACTION_CREDIT_ADJ
,TRANSACTION_CREDIT_ADJ_PRIMARY
,TRANSACTION_CREDIT_ADJ_SECONDARY
,TRANSACTION_DEBIT_ADJ
,TRANSACTION_DEBIT_ADJ_PRIMARY
,TRANSACTION_DEBIT_ADJ_SECONADRY
,LINE_ITEM_PAYMENTS
,LINE_ITEM_PAYMENTS_PRIMARY
,LINE_ITEM_PAYMENTS_SECONDARY
,LINE_ITEM_CONT_ADJ
,LINE_ITEM_CONT_ADJ_PRIMARY
,LINE_ITEM_CONT_ADJ_SECONDARY
,LINE_ITEM_REFUNDS
,SELF_PAY_LINE_ITEM_PAYMENTS
,INSURANCE_LINE_ITEM_PAYMENTS
,LINE_ITEM_CREDIT_ADJ
,LINE_ITEM_CREDIT_ADJ_PRIMARY
,LINE_ITEM_CREDIT_ADJ_SECONDARY
,LINE_ITEM_DEBIT_ADJ
,LINE_ITEM_DEBIT_ADJ_PRIMARY
,LINE_ITEM_DEBIT_ADJ_SECONDARY
,LINE_ITEM_DISCOUNTS
,LINE_ITEM_BAD_DEBT
,MOST_RECENT_NON_OUT_REJECTION_POSTING_DATE
,MOST_RECENT_NON_OUT_REJECTION_POSTING_PERIOD
,MOST_RECENT_NON_OUT_REJECTION_CODE
,PAYMENT_WITH_REJECTION_INDICATOR
,REJECTION_FSC
,DIAGNOSIS_1
,DIAGNOSIS_2
,DIAGNOSIS_3
,DIAGNOSIS_4
,NEW_INVOICES
,CORRECTED_INVOICE_INDICATOR
,NON_CORRECTED_INVOICE_INDICATOR
,OPEN_CLOSED_INVOICES_INDICATOR
,REVERSED_INVOICES_INDICATOR
,SPLIT_INVOICE_INDICATOR
,SOURCE
,TES_BATCH_NUMBER
,TES_ENCOUNTER_NUMBER
,TES_ENCOUNTER_CREATION_DATE
,TES_USER
,FIRST_PAYMENT_FSC
,FIRST_PAYMENT_DATE
,FIRST_PAYMENT_PERIOD
,MOST_RECENT_CLAIM_RUN_DATE
,FIRST_CLAIM_RUN_DATE
,CURRENT_YEAR_RVU
,SERVICE_YEAR_RVU
,MODIFIER_1
,MODIFIER_2
,MODIFIER_3
)

SELECT
FT.GROUP_NUMBER
,FT.INVOICE_NUMBER
,FT.TRANSACTION_SEQ
,FT.PROVIDER_SK
,FT.SECONDARY_PROVIDER_ID
,FT.PATIENT_ID
,FT.REFERRING_PHYSICIAN_ID
,FT.REFERRING_PHYSICIAN_NAME
,FT.ORIGINAL_FSC
,FT.REGISTRATION_FSC
,FT.INVOICE_FSC
,FT.CERT_NUMBER
,FT.INVOICE_CREATION_DATE
,FT.INVOICE_CREATION_PERIOD
,FT.LOCATION
,FT.TES_EXTRACTION_DATE
,FT.TES_RECEIVED_DATE
,FT.INVOICE_BALANCE
,FT.SERVICE_DATE
,FT.POSTING_PERIOD
,FT.POSTING_PERIOD_DATE
,FT.POSTING_DATE
,FT.ID_1
,FT.PROCEDURE_COUNT_ACT
,FT.PROCEDURE_COUNT_MOD
,FT.MODIFIER
,FT.CURRENT_YEAR_RVU_SOURCE
,FT.SERVICE_YEAR_RVU_SOURCE
,FT.CHARGES
,FT.START_TIME
,FT.UNITS_BASE
,FT.UNITS_ADDITIONAL
,FT.UNITS_DURATION
,FT.UNITS_TIME
,FT.UNITS_TOTAL
,FT.TOTAL_TRANSACTION_PAYMENTS
,CASE WHEN FT.ORIGINAL_FSC=FT.IT_ID_19 THEN ISNULL(FT.TOTAL_TRANSACTION_PAYMENTS,0) END 'TOTAL_TRANSACTION_PAYMENTS_PRIMARY'
,CASE WHEN FT.ORIGINAL_FSC<>FT.IT_ID_19 THEN ISNULL(FT.TOTAL_TRANSACTION_PAYMENTS,0) END 'TOTAL_TRANSACTION_PAYMENTS_SECONDARY'
,FT.TRANSACTION_CONT_ADJ
,CASE WHEN FT.ORIGINAL_FSC=FT.IT_ID_19 THEN ISNULL(FT.TRANSACTION_CONT_ADJ,0) END 'TRANSACTION_CONT_ADJ_PRIMARY'
,CASE WHEN FT.ORIGINAL_FSC<>FT.IT_ID_19 THEN ISNULL(FT.TRANSACTION_CONT_ADJ,0) END 'TRANSACTION_CONT_ADJ_SECONDARY'
,FT.TRANSACTION_CREDIT_ADJ
,CASE WHEN FT.ORIGINAL_FSC=FT.IT_ID_19 THEN ISNULL(FT.TRANSACTION_CREDIT_ADJ,0) END 'TRANSACTION_CREDIT_ADJ_PRIMARY'
,CASE WHEN FT.ORIGINAL_FSC<>FT.IT_ID_19 THEN ISNULL(FT.TRANSACTION_CREDIT_ADJ,0) END 'TRANSACTION_CREDIT_ADJ_SECONDARY'
,FT.TRANSACTION_DEBIT_ADJ
,CASE WHEN FT.ORIGINAL_FSC=FT.IT_ID_19 THEN ISNULL(FT.TRANSACTION_DEBIT_ADJ,0) END 'TRANSACTION_DEBIT_ADJ_PRIMARY'
,CASE WHEN FT.ORIGINAL_FSC<>FT.IT_ID_19 THEN ISNULL(FT.TRANSACTION_DEBIT_ADJ,0) END 'TRANSACTION_DEBIT_ADJ_SECONADRY'
,FT.LINE_ITEM_PAYMENTS
,FT.LINE_ITEM_PAYMENTS_PRIMARY
,FT.LINE_ITEM_PAYMENTS_SECONDARY
,FT.LINE_ITEM_CONT_ADJ
,FT.LINE_ITEM_CONT_ADJ_PRIMARY
,FT.LINE_ITEM_CONT_ADJ_SECONDARY
,FT.LINE_ITEM_REFUNDS
,0 'SELF_PAY_LINE_ITEM_PAYMENTS'
,0 'INSURANCE_LINE_ITEM_PAYMENTS'
,FT.LINE_ITEM_CREDIT_ADJ
,FT.LINE_ITEM_CREDIT_ADJ_PRIMARY
,FT.LINE_ITEM_CREDIT_ADJ_SECONDARY
,FT.LINE_ITEM_DEBIT_ADJ
,FT.LINE_ITEM_DEBIT_ADJ_PRIMARY
,FT.LINE_ITEM_DEBIT_ADJ_SECONDARY
,FT.LINE_ITEM_DISCOUNTS
,FT.LINE_ITEM_BAD_DEBT
,FT.MOST_RECENT_NON_OUT_REJECTION_POSTING_DATE
,FT.MOST_RECENT_NON_OUT_REJECTION_POSTING_PERIOD
,FT.MOST_RECENT_NON_OUT_REJECTION_CODE
,FT.PAYMENT_WITH_REJECTION_INDICATOR
,FT.REJECTION_FSC
,FT.DIAGNOSIS_1
,FT.DIAGNOSIS_2
,FT.DIAGNOSIS_3
,FT.DIAGNOSIS_4
,FT.NEW_INVOICES
,FT.CORRECTED_INVOICE_INDICATOR
,FT.NON_CORRECTED_INVOICE_INDICATOR
,FT.OPEN_CLOSED_INVOICES_INDICATOR
,FT.REVERSED_INVOICES_INDICATOR
,FT.SPLIT_INVOICE_INDICATOR
,FT.SOURCE
,FT.TES_BATCH_NUMBER
,FT.TES_ENCOUNTER_NUMBER
,FT.TES_ENCOUNTER_CREATION_DATE
,FT.TES_USER
,FT.FIRST_PAYMENT_FSC
,FT.FIRST_PAYMENT_DATE
,FT.FIRST_PAYMENT_PERIOD
,FT.MOST_RECENT_CLAIM_RUN_DATE
,FT.FIRST_CLAIM_RUN_DATE
,FT.CURRENT_YEAR_RVU
,FT.SERVICE_YEAR_RVU
,FT.MODIFIER_1
,FT.MODIFIER_2
,FT.MODIFIER_3

FROM FACT_SERVICE_REJECTION_WORKING_DAILY FT
ORDER BY 1,2,3

As i am doing this in production serer i need to delete the record before running the above

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 13:10:32

didnt you still understand what i've given you?

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-01-26 : 13:12:06

No Visakh, pls explain

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-01-26 : 13:13:26

Please help me with above my query as i need to do this very urgent..

Thanks for your kind reply

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 13:22:23

whats the primary key group based on which you need to distinguish duplicates?

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-01-26 : 13:28:47

The primary key group is on INVOICE_NUMBER,TRANSACTION_SEQ on fact_service_rejection table.

Please help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 13:30:46

quote:
Originally posted by gangadhara.ms

The primary key group is on INVOICE_NUMBER,TRANSACTION_SEQ on fact_service_rejection table.

Please help.

so each of the repeated ones of above group, which single record you need to retain in table? based on what column you determine order?

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-01-26 : 13:40:33

not sure exactly..i guess with invoice_number

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources