Ignore "dupes" when importing using SELECT

Question

Post reply

Ignore "dupes" when importing using SELECT

MrBaseball34

SSCrazy

Points: 2515
More actions
October 6, 2008 at 9:58 am

#213709

In writing a conversion script for data from a competitor's application, I am trying to pull
CPTCodes from a table called billing procedures into a table named coChargeCodes. The
problem in the code below is that, because the description column was "free-text", many
of the descriptions do not match the other descriptions for the same CPTCode.
Is there a way for me to pull out only the first one in the select list? (preferably the one in all caps)
INSERT INTO coChargeCodes
(ChargeCode, ChargeCategoryID, ChargeTypeID, Description,
AlternateDescription,
Units, Fee, CreatedByUserID, CreatedDtTm)
SELECT DISTINCT CPTCode AS ChargeCode, 1 AS ChargeCategoryID, 1 AS ChargeTypeID,
Description AS Description, '' AS AlternateDescription,
units, charge, 1 AS CreatedByUserID, GETDATE() AS Expr31
FROM billingProcedures
Here is some sample data:
10061, 1, 1, 'I&D OF ABSCESS; COMPLICATED OR MULTIPLE', '', 1, 400.00, 1, '2008-10-06 10:51:36.963'
10061, 1, 1, 'I&D ABSC COMP/MLT', '', 1, 400.00, 1, '2008-10-06 10:51:36.963'
10081, 1, 1, 'I&d pilonidal cst comp', '', 1, 400.00, 1, '2008-10-06 10:51:36.963'
10121, 1, 1, 'INC&RMVL FB SUBQ TISS COMP', '', 1, 350.00, 1, '2008-10-06 10:51:36.963'
10140, 1, 1, 'I&d hmtma seroma/flu collj', '', 1, 300.00, 1, '2008-10-06 10:51:36.963'
10140, 1, 1, 'I&d hmtma seroma/flu collj', '', 1, 300.00, 1, '2008-10-06 10:51:36.963'
11000, 1, 1, 'DBRDMT X10SV ECZMT/INFCT SKN UP 10% BDY SURF', '', 1, 150.00, 1, '2008-10-06 10:51:36.963'
11000, 1, 1, 'Dbrdmt x10sv eczmt/infct skn up 10% bdy surf', '', 1, 150.00, 1, '2008-10-06 10:51:36.963'
11042, 1, 1, 'DBRDMT SKN&SUBQ TISS', '', 1, 200.00, 1, '2008-10-06 10:51:36.963'
11042, 1, 1, 'DBRDMT SKN&SUBQ TISS', '', 1, 200.00, 1, '2008-10-06 10:51:36.963'
11042, 1, 1, 'DBRDMT SKN&SUBQ TISS', '', 1, 200.00, 1, '2008-10-06 10:51:36.963'
11042, 1, 1, 'DBRDMT SKN&SUBQ TISS', '', 1, 200.00, 1, '2008-10-06 10:51:36.963'
11042, 1, 1, 'Dbrdmt skn&subq tiss', '', 1, 200.00, 1, '2008-10-06 10:51:36.963'
11042, 1, 1, 'Removal of damaged skin and underlying tissue', '', 1, 200.00, 1, '2008-10-06 10:51:36.963'
11043, 1, 1, 'DBRDMT SKN SUBQ TISS&MUSC', '', 1, 585.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKN SUBQ/MUC MEMB 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKN SUBQ/MUC MEMB 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKN SUBQ/MUC MEMB 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKN SUBQ/MUC MEMB 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKN SUBQ/MUC MEMB 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKN SUBQ/MUC MEMB 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 88.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 200.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 104.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISS&/MUCOUS MEMB (SEP PRO); 1 LES', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 0.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; 1 LESION', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 200.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 2, 166.00, 1, '2008-10-06 10:51:36.963'
11100, 1, 1, 'Bx skn subq/muc memb 1 lesion', '', 1, 83.00, 1, '2008-10-06 10:51:36.963'
11101, 1, 1, 'Bx skn subq/muc memb ea spx addl lesion', '', 2, 150.00, 1, '2008-10-06 10:51:36.963'
11101, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; EA ADD', '', 1, 77.00, 1, '2008-10-06 10:51:36.963'
11101, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; EA ADD', '', 1, 77.00, 1, '2008-10-06 10:51:36.963'
11101, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; EA ADD', '', 1, 77.00, 1, '2008-10-06 10:51:36.963'
11101, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; EA ADD', '', 1, 77.00, 1, '2008-10-06 10:51:36.963'
11101, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; EA ADD', '', 1, 77.00, 1, '2008-10-06 10:51:36.963'
11101, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; EA ADD', '', 1, 77.00, 1, '2008-10-06 10:51:36.963'
11101, 1, 1, 'BX SKIN SUBQ TISSUE &/ MUCOUS MEMBRANE; EA ADD', '', 2, 154.00, 1, '2008-10-06 10:51:36.963'
11101, 1, 1, 'BX SKIN SUBQ TISS&/MUCOUS MEMB (SEP PRO); EA ADD', '', 2, 154.00, 1, '2008-10-06 10:51:36.963'
11101, 1, 1, 'Bx skn subq/muc memb ea spx addl lesion', '', 1, 77.00, 1, '2008-10-06 10:51:36.963'
11101, 1, 1, 'Bx skn subq/muc memb ea spx addl lesion', '', 1, 77.00, 1, '2008-10-06 10:51:36.963'
11101, 1, 1, 'BX SKN SUBQ/MUC MEMB EA SPX ADDL LESION', '', 2, 154.00, 1, '2008-10-06 10:51:36.963'
11101, 1, 1, 'Bx skn subq/muc memb ea spx addl lesion', '', 1, 77.00, 1, '2008-10-06 10:51:36.963'
11101, 1, 1, 'BX SKN SUBQ/MUC MEMB EA SPX ADDL LESION', '', 1, 77.00, 1, '2008-10-06 10:51:36.963'
11101, 1, 1, 'BX SKN SUBQ/MUC MEMB EA SPX ADDL LESION', '', 1, 77.00, 1, '2008-10-06 10:51:36.963'
11200, 1, 1, 'RMVL SK TGS MLT FIBRQ TAGS ANY AREA UP&W/15', '', 1, 50.00, 1, '2008-10-06 10:51:36.963'
11200, 1, 1, 'Rmvl sk tgs mlt fibrq tags any area up&w/15 <', '', 1, 95.00, 1, '2008-10-06 10:51:36.963'

Viewing 3 posts - 1 through 2 (of 2 total)

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

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 1

Nevermind, found the answer here:

http://www.sqlservercentral.com/Forums/Topic581008-338-1.aspx

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hi Mr Baseball

If I'm teaching you to sucks eggs then I humbly apologise now...best practice in this scenario is to use a "staging table" as the target for your import, then copy the data from there to your destination table after it's been formatted/verified/deduped etc.

It will save you a whole world of the deep brown stuff if you can adopt this approach.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden