Insert Statement - 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

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]

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

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)

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

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.

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)

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?

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-24 : 10:41:20

because there are no rows that match.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources