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
 General SQL Server Forums
 New to SQL Server Programming
 Insert Statement

Author  Topic 

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-11-21 : 10:30:43
Hi,

I have the following INSERT statement,

   INSERT INTO dbo.CPRDLkupMedical (Snomed,Acode,BCode,Term)
SELECT DISTINCT Snomed,Acode,BCode,Term
FROM dbo.CPRDALLMedicalCodes WITH (tablock)


dbo.CPRDALLMedicalCodes is shown below

Snomed Acode BCode Term
10 6Fgd 18G NULL
11 8235 sd76 NULL
12 EMIDTT_1 NULL Attachment
12 EMIDTT_2 NULL Attachment
12 EMIDTT_3 NULL Attachment
12 EMIDTT_4 NULL Attachment



In the dbo.CPRDLkupMedical it inserted all the records since they are distinct.


dbo.CPRDLkupMedical is as expected

Snomed Acode BCode Term
10 6Fgd 18G NULL
11 8235 sd76 NULL
12 EMIDTT_1 NULL Attachment
12 EMIDTT_2 NULL Attachment
12 EMIDTT_3 NULL Attachment
12 EMIDTT_4 NULL Attachment


Now, Instead I want it to insert ONLY the following records as shown



Snomed Acode BCode Term
10 6Fgd 18G NULL
11 8235 sd76 NULL
12 EMIDTT NULL Attachment



Notice - Acode is ONLY considering EMIDTT ONLY as the field data to determine the distinction. It ignores the _1, _2, _3 etc

Also, I want to update the INSERT statement so that at one run I will exclude all the other records not required.

Any help please...

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-21 : 10:35:45
[code]
INSERT INTO dbo.CPRDLkupMedical (Snomed,Acode,BCode,Term)
SELECT DISTINCT Snomed,
case WHEN left(acode,6) = 'EMIDTT' then 'EMIDTT' ELSE acode end
Acode,BCode,Term
FROM dbo.CPRDALLMedicalCodes WITH (tablock)
[/code]
Go to Top of Page

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-11-24 : 07:45:09
This is Very OK.. But I have updated the CASE STATEMENT with another criteria and added another CASE.. Will this work?? Please review
N/B: This now is considering millions of records rather than the above ONLY.


INSERT INTO dbo.CPRDLkupMedical (Snomed,Acode,BCode,Term)
SELECT DISTINCT Snomed,
case WHEN left(Acode,6) = 'EMIDTT' AND snomed = '3261000006107' THEN 'EMIDTT' ELSE Acode end
case WHEN left(Acode, 6)= 'PCSDT' AND snomed = '416118004' THEN 'PCSDT' ELSE Acode end
Acode,BCode,Term
FROM dbo.CPRDALLMedicalCodes WITH (tablock)



Thanks

Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-24 : 08:57:06
Did you actually try to run this? (Your syntax is incorrect)
Go to Top of Page

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-11-24 : 09:03:50
Before I run it please could you counter check for me.. It is a huge table of millions of records - Unless am sure it is OK - thats when I will run it..

Thanks
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-24 : 09:18:06
First, fix your syntax. In SSMS, hit Ctrl_F5 and fix the errors that it shows. (Remember that you really should only post syntactically-correct queries here).

Also...millions of records is only a medium-size table these days. For huge, you'll need billions.
Go to Top of Page

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-11-24 : 09:46:21
This worked, however, it also included the duplicates and never considered the CASE statement. Please what am I doing wrong?



INSERT INTO dbo.CPRDLkupMedicalNew (Snomed,Acode,BCode,Term)
SELECT DISTINCT Snomed,
case WHEN left(Acode,6) = 'EMIDTT' AND snomed = '3261000006107' THEN 'EMIDTT'
WHEN left(Acode,6)= 'PCSDT' AND snomed = '416118004' THEN 'PCSDT' ELSE Acode end
Acode,BCode,Term
FROM dbo.CPRDALLMedicalCodes WITH (tablock)

Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-24 : 10:08:41
run this and analyze the output:

select snomed, acode, bcode, term
from dbo.CPRDALLMedicalCodes
where left(Acode,6) = 'EMIDTT' AND snomed = '3261000006107'
or left(Acode,5)= 'PCSDT' AND snomed = '416118004'

that will let you see if there are any hits. Plus notice in your CASE, the second WHEN has left(Acode, 6) but only compares 5 characters, which would be padded with a blank on the right. Is that what you want?
Go to Top of Page

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-11-24 : 10:35:29
Ok tested the above and it is not outputing when the snomed = 416118004 and Acode = PCSDT

Note - I rectified the left (Acode,5) .. Thanks

So, why doesnt it pick the second snomed and do excatly as the first case?

Thanks
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-24 : 10:41:20
because there are no rows that match.
Go to Top of Page
   

- Advertisement -