insert unique records SQL 2005 1

[vba317]

I am using SQL 2005.Currently If I try and do the insert part of the code I am getting a primary key violation. I am trying to write a query to insert unique records into an existing table. Before I do this I am using select statements to verify the number of records is correct before I do the insert. Currently I am stumped on what to do. The dbo.tmp_rpt_dic_CPT table has 611 records and currently the dbo.rpt_dic_CPT table had 478 records in it. I am expecting that if my select query is done correctly my query would return 133 records. It is currently returning 390 records. clntid,cptcode and cptcomp are primary keys. If I got the correct number I would turn the query into a delete query so I could then delete the dups from the dbo.tmp_rpt_dic_CPT table and insert the unique records into the rptdata_monthly.dbo.rpt_dic_CPT table. Any help would be appreciated.

Tom

INSERT INTO rptdata_monthly.dbo.rpt_dic_CPT

SELECT DISTINCT CPT1.clntid,CPT1.cptcode,CPT1.cptcomp,CPT1.cptdesc,CPT1.cptrpt
FROM rptdata_monthly.dbo.rpt_dic_CPT CPT1
JOIN dbo.tmp_rpt_dic_CPT CPT2 ON CPT1.clntid = CPT2.clntid and CPT1.cptcode = CPT2.cptcode and CPT1.cptcomp = CPT2.cptcomp
WHERE CPT1.clntid= 38
GROUP BY CPT1.clntid,CPT1.cptcode,CPT1.cptcomp,CPT1.cptdesc,CPT1.cptrpt
ORDER BY CPT1.clntid,cptcode,CPT1.cptcomp,CPT1.cptdesc,CPT1.cptrpt

 

[gmmastros]

You said, "clntid,cptcode and cptcomp are primary keys."

You are selecting:
CPT1.clntid,CPT1.cptcode,CPT1.cptcomp,CPT1.cptdesc,CPT1.cptrpt

Since you are also returning cptdesc and cptrpt, there must be rows returned that have the same clntid, cptcode and cptcomp but also have different cptdesc and/or cptrpt.

To find these rows....

SELECT  CPT1.clntid,CPT1.cptcode,CPT1.cptcomp
FROM    rptdata_monthly.dbo.rpt_dic_CPT CPT1
        JOIN dbo.tmp_rpt_dic_CPT CPT2 
          ON CPT1.clntid = CPT2.clntid 
          and CPT1.cptcode = CPT2.cptcode 
          and CPT1.cptcomp = CPT2.cptcomp
WHERE   CPT1.clntid= 38
GROUP BY CPT1.clntid,CPT1.cptcode,CPT1.cptcomp,CPT1.cptdesc,CPT1.cptrpt
Having Count(*) > 1

The query above will only return 1 row for each combination of CPT1.clntid, CPT1.cptcode, CPT1.cptcomp. More importantly, if you then select the data that matches the returned rows, you will see that there are different cptdesc and/or cptrpt. You must decide what to do about these duplicates before you can proceed.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vba317]

George, Unfortunately your query returned 0 records. What matters the most to me is getting unique cptcodes. I am wondering if my issue is that it is a varchar field?

Tom

 

[gmmastros]

There was a mistake in my query. Sorry for that. Try this instead.

SELECT  CPT1.clntid,CPT1.cptcode,CPT1.cptcomp
FROM    rptdata_monthly.dbo.rpt_dic_CPT CPT1
        JOIN dbo.tmp_rpt_dic_CPT CPT2 
          ON CPT1.clntid = CPT2.clntid 
          and CPT1.cptcode = CPT2.cptcode 
          and CPT1.cptcomp = CPT2.cptcomp
WHERE   CPT1.clntid= 38
GROUP BY CPT1.clntid,CPT1.cptcode,CPT1.cptcomp
Having Count(*) > 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vba317]

George,
Sorry I still get 0 records returned. I know there are duplicates because if I try to do an insert into rptdata_monthly.dbo.rpt_dic_CPT from dbo.tmp_rpt_dic_CPT I get a primary key violation.

Tom

 

[gmmastros]

When you are inserting data in to a existing table that already has data, you can get this error in several ways.

I originally thought that the problem was because you were selecting duplicates. Meaning... the data you were trying to insert has duplicates. Since the 2nd query above returns 0 rows, this is apparently not the case.

The other way to get this error is by trying to insert data that already exists. I suspect this must be your problem. Whenever I encounter a situation like this, I usually end up using 2 queries instead of just 1. The first query would update the data and the second would insert the missing data.

Ex:

Update  CPT1
Set     CPT1.cptdesc = CPT2.cptdesc,
        CPT1.cptrpt = CPT2.cptrpt
FROM    rptdata_monthly.dbo.rpt_dic_CPT CPT1
        JOIN dbo.tmp_rpt_dic_CPT CPT2 
          ON CPT1.clntid = CPT2.clntid 
          and CPT1.cptcode = CPT2.cptcode 
          and CPT1.cptcomp = CPT2.cptcomp
WHERE   CPT1.clntid= 38

And then insert the missing data like this:

INSERT 
INTO    rptdata_monthly.dbo.rpt_dic_CPT(
          clntid,
          cptcode,
          cptcomp,
          cptdesc,
          cptrpt)
Select  CPT2.clntid,
        CPT2.cptcode,
        CPT2.cptcomp,
        CPT2.cptdesc,
        CPT2.cptrpt
FROM    dbo.tmp_rpt_dic_CPT CPT2 
        LEFT JOIN rptdata_monthly.dbo.rpt_dic_CPT CPT1
          ON  CPT2.clntid = CPT1.clntid 
          and CPT2.cptcode = CPT1.cptcode 
          and CPT2.cptcomp = CPT1.cptcomp
WHERE   CPT2.clntid= 38
        And CPT1.clntid Is NULL

This code is not tested, but I suspect it will do what you want, possibly with some minor tweaks.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

INSERT INTO rptdata_monthly.dbo.rpt_dic_CPT SELECT DISTINCT CPT1.... FROM rptdata_monthly.dbo.rpt_dic_CPT CPT1

If you insert data from a table rptdata_monthly.dbo.rpt_dic_CPT (with alias name CPT1) into the same table rptdata_monthly.dbo.rpt_dic_CPT, then it is no wonder you get repeating keys, is it?

Bye, Olaf.

 

[vba317]

George,
Thanks you! You are right this issue was trying to insert records into a table where the records already exist. I tried your last update and insert statements and they did the trick. This has been an issue for months I have been trying to resolve. I have 14 dictionaries that I need to update on a daily basis. I will use this concept to update them .

Tom