don''t know how to use a cursor to insert rows from one table to another elinimating dups

Question

Post reply

don''t know how to use a cursor to insert rows from one table to another elinimating dups

Tom Currier

Old Hand

Points: 375
More actions
June 12, 2006 at 10:04 am

#153038

I know this is a really common issue, but the solution escapes me
/*** What I'm trying to do here is check for the existance of a record {e.g., key violation}
while inserting records from a table that contains many duplicates.
I've also tried using a NOT EXISTS in the WHERE clause without the
LEFT OUTER JOIN, e.g., SELECT ... FROM #TEMP_WEB_NOTES - I posted this problem in the newbie
section last week and received one reply that did not use a cursor and did not work.
I realize that I must use a cursor for this operation but have no real clue as to how
to do this. So...I need to insert records from web_note_1 into temp_web_notes
where the following is true:
web_note_1 contains dups, e.g. no primary key
CC025_ORG_CODE VARCHAR(2)
CC025_EXT_ACCT_CODE VARCHAR(8)
CC025_NOTE_CLASS VARCHAR(6)
CC025_PROD_CODE VARCHAR(24)
CC025_NOTE_TEXT VARCHAR(2000)
CC025_HTML_TEXT VARCHAR(2000)
TEMP_WEB_NOTES Primary key is
CC025_ORG_CODE
CC025_EXT_ACCT_CODE
CC025_NOTE_CLASS
CC025_PROD_CODE
Tom

Viewing 11 posts - 1 through 10 (of 10 total)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

So following code will not work?

SELECT web_note_1.CC025_ORG_CODE,

web_note_1.CC025_EXT_ACCT_CODE,

web_note_1.CC025_NOTE_CLASS,

web_note_1.CC025_PROD_CODE

LEFT JOIN TEMP_WEB_NOTES ON TEMP_WEB_NOTES.CC025_ORG_CODE = web_note_1.CC025_ORG_CODE

AND TEMP_WEB_NOTES.CC025_EXT_ACCT_CODE = web_note_1.CC025_EXT_ACCT_CODE

AND TEMP_WEB_NOTES.CC025_NOTE_CLASS = web_note_1.CC025_NOTE_CLASS

AND TEMP_WEB_NOTES.CC025_PROD_CODE = web_note_1.CC025_PROD_CODE

WHERE TEMP_WEB_NOTES.CC025_ORG_CODE IS NULL

GROUP BY web_note_1.CC025_ORG_CODE,

web_note_1.CC025_EXT_ACCT_CODE,

web_note_1.CC025_NOTE_CLASS,

web_note_1.CC025_PROD_CODE

HAVING COUNT(*) > 1

Do you have some test data to show?

N 56°04'39.16"
E 12°55'05.25"

Tom Currier Old Hand Points: 375 More actions · Answer 2

Sample problem data

ORG ACCT CLASS PROD NOTE_TEXT HTML_TEXT

---- -------- ------ ------- --------------- ---------------

10 0000256 MEPG DECORAT One of the Bay <DIV>One of the

10 0000256 MEPG PRODCOS One of the Bay <DIV>One of the

10 0000256 MEPG STAGING One of the Bay <DIV>One of the

10 0038959 WEB AVSVCS Rental of compu Rental of compu

10 0038959 WEB AVSVCS Rental of LCD/o Rental of LCD/o

10 0038959 WEB COMPREN Rental of compu Rental of compu

10 0038959 WEB COMPREN Rental of LCD/o Rental of LCD/o

Need following Output

ORG ACCT CLASS PROD NOTE_TEXT HTML_TEXT

---- -------- ------ ------- --------------- ---------------

10 0000256 MEPG DECORAT One of the Bay <DIV>One of the

10 0000256 MEPG PRODCOS One of the Bay <DIV>One of the

10 0000256 MEPG STAGING One of the Bay <DIV>One of the

10 0038959 WEB AVSVCS Rental of compu Rental of compu

10 0038959 WEB COMPREN Rental of compu Rental of compu

Where ORG, ACCT, CLASS & PROD ARE UNIQUE KEY IN NEW TABLE

and Note Text and HTML text may be the same or different, but need first instance

if dups exist.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

>>I need to get the first occurrance of the existing note

The solution starts by identifying the business rule that defines "first".

What is the "first note" ? Is there a date/time column available to identify it ?

Tom Currier Old Hand Points: 375 More actions · Answer 4

See sample data, first note, being the 'lowest value note' {in other words we don't care about which specific note text, what's important is writing a unique record where the PK is ORG, ACCT, CLASS & PROD - That's

where I'm stuck.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

So, just arbitrary notes for each keyset ?

SELECT ORG, ACCT, CLASS, PROD,

MIN(NOTE_TEXT) AS NOTE_TEXT, MIN(HTML_TEXT) AS HTML_TEXT

FROM YourTable

GROUP BY ORG, ACCT, CLASS, PROD

Tom Currier Old Hand Points: 375 More actions · Answer 6

Thanks a million, this worked! I guess it was just too simple for me to get!

Tom Currier Old Hand Points: 375 More actions · Answer 7

Thanks a million; I guess the solution was just too simple for me to get

Vladan SSC-Insane Points: 22072 More actions · Answer 8

There is only one thing to be aware of when using this solution... MIN(NOTE_TEXT) and MIN(HTML_TEXT) are independent and the minimum value from each column is returned - which means that sometimes you could end up with a row that took NOTE from one and HTML from another of the duplicate rows. In you posted example this will not happen, but if you have duplicity like this:

ORG ACCT CLASS PROD NOTE_TEXT HTML_TEXT

---- -------- ------ ------- --------------- ---------------

10 0000256 MEPG DECORAT One of the Bay <DIV>1. One of the

10 0000256 MEPG DECORAT Bay, One of the <DIV>One of the

result will be:

10 0000256 MEPG DECORAT Bay, One of the <DIV>1. One of the

If this is not a problem, go ahead.

stax68 SSChampion Points: 11711 More actions · Answer 9

If you do need to make sure the two text columns come from the same row, you'll need to do something like:

select V.ORG, V.ACCT, V.CLASS, V.PROD, t.NOTE_TEXT, t.HTML_TEXT
from(
SELECT ORG, ACCT, CLASS, PROD, MIN(KEY) KEY FROM YourTable GROUP BY ORG, ACCT, CLASS, PROD ) V
join YourTable t
on t.KEY = V.KEY

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 10

As I wrote yesterday?

N 56°04'39.16"
E 12°55'05.25"