August 4, 2010 at 10:52 pm
SETUP:
CREATE TABLE [dbo].[MEDICINES](
[MEDICINE_NUM] [int] IDENTITY(1,1) NOT NULL,
[MEDICINE_NAME] [varchar](60) NOT NULL,
[MEDI_CATG_NUM] [int] NOT NULL,
[MEDICINE_COMM_TXT] [varchar](255) NULL,
[STATUS_FLG] [varchar](1) NULL,
[CREATED_BY] [int] NOT NULL,
[CREATED_DT] [datetime] NOT NULL,
[OFFICE_NUM] [int] NOT NULL,
[EXT_MED_NUM] [varchar](10) NULL,
[EXT_MED_FLG] [bit] NULL,
CONSTRAINT [MS11_PK] PRIMARY KEY NONCLUSTERED
(
[MEDICINE_NUM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
) ON [PRIMARY]
GO
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
SET IDENTITY_INSERT [dbo].[MEDICINES] ON;
BEGIN TRANSACTION;
INSERT INTO [dbo].[MEDICINES]([MEDICINE_NUM], [MEDICINE_NAME], [MEDI_CATG_NUM], [MEDICINE_COMM_TXT], [STATUS_FLG], [CREATED_BY], [CREATED_DT], [OFFICE_NUM], [EXT_MED_NUM], [EXT_MED_FLG])
SELECT 1, N'Naprosyn', 3, NULL, N'N', 1, '20070119 14:09:34.700', 1, NULL, NULL UNION ALL
SELECT 2, N'Ibuprofen', 3, NULL, N'N', 1, '20070119 14:09:34.733', 1, NULL, NULL UNION ALL
SELECT 3, N'Relafen', 3, NULL, N'N', 1, '20070119 14:09:34.747', 1, NULL, NULL UNION ALL
SELECT 4, N'arthrotec', 3, NULL, N'N', 1, '20070119 14:09:34.763', 1, NULL, NULL UNION ALL
SELECT 5, N'Voltaren', 3, NULL, N'N', 1, '20070119 14:09:34.780', 1, NULL, NULL UNION ALL
SELECT 6, N'Vioxx', 3, NULL, N'N', 1, '20070119 14:09:34.793', 1, NULL, NULL UNION ALL
SELECT 7, N'Celebrex', 3, NULL, N'N', 1, '20070119 14:09:34.810', 1, NULL, NULL UNION ALL
SELECT 8, N'Bextra', 3, NULL, N'N', 1, '20070119 14:09:34.827', 1, NULL, NULL UNION ALL
SELECT 9, N'Tylenol', 3, NULL, N'N', 1, '20070119 14:09:34.840', 1, NULL, NULL UNION ALL
SELECT 10, N'aspirin', 3, NULL, N'N', 1, '20070119 14:09:34.857', 1, NULL, NULL UNION ALL
SELECT 11, N'Skelaxin', 4, NULL, N'N', 1, '20070119 14:09:34.873', 1, NULL, NULL UNION ALL
SELECT 12, N'Flexeril', 4, NULL, N'N', 1, '20070119 14:09:34.890', 1, NULL, NULL UNION ALL
SELECT 13, N'Soma', 4, NULL, N'N', 1, '20070119 14:09:34.903', 1, NULL, NULL UNION ALL
SELECT 14, N'Robaxam', 4, NULL, N'N', 1, '20070119 14:09:34.920', 1, NULL, NULL UNION ALL
SELECT 15, N'Vicodin', 5, NULL, N'N', 1, '20070119 14:09:34.937', 1, NULL, NULL UNION ALL
SELECT 16, N'Vicodin es', 5, NULL, N'N', 1, '20070119 14:09:34.950', 1, NULL, NULL UNION ALL
SELECT 17, N'Darvocet', 5, NULL, N'N', 1, '20070119 14:09:34.967', 1, NULL, NULL UNION ALL
SELECT 18, N'Tylenol#3', 5, NULL, N'N', 1, '20070119 14:09:34.983', 1, NULL, NULL UNION ALL
SELECT 19, N'Tylenol#4', 5, NULL, N'N', 1, '20070119 14:09:34.997', 1, NULL, NULL UNION ALL
SELECT 20, N'Norco (5/7.5/10)-comes in all three', 5, NULL, N'N', 1, '20070119 14:09:35.013', 1, NULL, NULL UNION ALL
SELECT 21, N'Ultram', 5, NULL, N'N', 1, '20070119 14:09:35.030', 1, NULL, NULL UNION ALL
SELECT 22, N'Ultracet', 5, NULL, N'N', 1, '20070119 14:09:35.043', 1, NULL, NULL UNION ALL
SELECT 23, N'Percocet', 5, NULL, N'N', 1, '20070119 14:09:35.060', 1, NULL, NULL UNION ALL
SELECT 24, N'Oxycontin', 5, NULL, N'N', 1, '20070119 14:09:35.077', 1, NULL, NULL UNION ALL
SELECT 25, N'MS Contin', 5, NULL, N'N', 1, '20070119 14:09:35.090', 1, NULL, NULL UNION ALL
SELECT 26, N'Duragesic ', 5, NULL, N'N', 1, '20070119 14:09:35.107', 1, NULL, NULL UNION ALL
SELECT 27, N'Actiq', 5, NULL, N'N', 1, '20070119 14:09:35.123', 1, NULL, NULL UNION ALL
SELECT 28, N'Morphine IR', 5, NULL, N'N', 1, '20070119 14:09:35.140', 1, NULL, NULL UNION ALL
SELECT 29, N'Valium', 6, NULL, N'N', 1, '20070119 14:09:35.153', 1, NULL, NULL UNION ALL
SELECT 30, N'Ativan', 6, NULL, N'N', 1, '20070119 14:09:35.170', 1, NULL, NULL UNION ALL
SELECT 31, N'Halcyon', 6, NULL, N'N', 1, '20070119 14:09:35.187', 1, NULL, NULL UNION ALL
SELECT 32, N'Temazepam', 6, NULL, N'N', 1, '20070119 14:09:35.200', 1, NULL, NULL UNION ALL
SELECT 33, N'Procardia', 7, NULL, N'N', 1, '20070119 14:09:35.217', 1, NULL, NULL UNION ALL
SELECT 34, N'Atenolol', 7, NULL, N'N', 1, '20070119 14:09:35.233', 1, NULL, NULL UNION ALL
SELECT 35, N'Labetolol', 7, NULL, N'N', 1, '20070119 14:09:35.247', 1, NULL, NULL UNION ALL
SELECT 36, N'Metoprolol', 7, NULL, N'N', 1, '20070119 14:09:35.263', 1, NULL, NULL UNION ALL
SELECT 37, N'Digoxin', 7, NULL, N'N', 1, '20070119 14:09:35.280', 1, NULL, NULL UNION ALL
SELECT 38, N'NTG (nitroglycerin)', 7, NULL, N'N', 1, '20070119 14:09:35.293', 1, NULL, NULL UNION ALL
SELECT 39, N'Clonidine', 7, NULL, N'N', 1, '20070119 14:09:35.310', 1, NULL, NULL UNION ALL
SELECT 40, N'HCTZ (hydrochlorothiazide)', 8, NULL, N'N', 1, '20070119 14:09:35.327', 1, NULL, NULL UNION ALL
SELECT 41, N'Lasix', 8, NULL, N'N', 1, '20070119 14:09:35.340', 1, NULL, NULL UNION ALL
SELECT 42, N'Spironolactone', 8, NULL, N'N', 1, '20070119 14:09:35.357', 1, NULL, NULL UNION ALL
SELECT 43, N'Prozac', 9, NULL, N'N', 1, '20070119 14:09:35.373', 1, NULL, NULL UNION ALL
SELECT 44, N'Wellbutrin', 9, NULL, N'N', 1, '20070119 14:09:35.390', 1, NULL, NULL UNION ALL
SELECT 45, N'Effexor', 9, NULL, N'N', 1, '20070119 14:09:35.403', 1, NULL, NULL UNION ALL
SELECT 46, N'Elavil', 9, NULL, N'N', 1, '20070119 14:09:35.420', 1, NULL, NULL UNION ALL
SELECT 47, N'Ambien', 1, NULL, N'N', 1, '20070614 16:54:24.000', 1, NULL, NULL UNION ALL
SELECT 48, N'sonnata', 10, NULL, N'N', 1, '20070119 14:09:35.450', 1, NULL, NULL UNION ALL
SELECT 49, N'benadryl', 11, NULL, N'N', 1, '20070119 14:09:35.467', 1, NULL, NULL UNION ALL
SELECT 50, N'lidoderm patch', 11, NULL, N'N', 1, '20070119 14:09:35.483', 1, NULL, NULL
COMMIT;
RAISERROR (N'[dbo].[MEDICINES]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[MEDICINES]([MEDICINE_NUM], [MEDICINE_NAME], [MEDI_CATG_NUM], [MEDICINE_COMM_TXT], [STATUS_FLG], [CREATED_BY], [CREATED_DT], [OFFICE_NUM], [EXT_MED_NUM], [EXT_MED_FLG])
SELECT 51, N'Zylaprem', 11, NULL, N'N', 1, '20070119 14:09:35.497', 1, NULL, NULL UNION ALL
SELECT 52, N'Avilade', 11, NULL, N'N', 1, '20070119 14:09:35.513', 1, NULL, NULL UNION ALL
SELECT 53, N'Lipitor', 11, NULL, N'N', 1, '20070119 14:09:35.530', 1, NULL, NULL UNION ALL
SELECT 54, N'Altace', 11, NULL, N'N', 1, '20070119 14:09:35.543', 1, NULL, NULL UNION ALL
SELECT 55, N'Glucophage', 11, NULL, N'N', 1, '20070119 14:09:35.560', 1, NULL, NULL UNION ALL
SELECT 56, N'Synthroid', 11, NULL, N'N', 1, '20070119 14:09:35.577', 1, NULL, NULL UNION ALL
SELECT 57, N'Sular', 11, NULL, N'N', 1, '20070119 14:09:35.590', 1, NULL, NULL UNION ALL
SELECT 58, N'Zoloft', 11, NULL, N'N', 1, '20070119 14:09:35.607', 1, NULL, NULL UNION ALL
SELECT 59, N'Pentanyl', 11, NULL, N'N', 1, '20070119 14:09:35.623', 1, NULL, NULL UNION ALL
SELECT 60, N'Morphine', 11, NULL, N'N', 1, '20070119 14:09:35.640', 1, NULL, NULL UNION ALL
SELECT 61, N'Backlofen', 11, NULL, N'N', 1, '20070119 14:09:35.653', 1, NULL, NULL UNION ALL
SELECT 63, N'Medicine1', 11, NULL, N'N', 38, '20070608 11:27:10.000', 1, NULL, NULL UNION ALL
SELECT 64, N'mn', 11, NULL, N'N', 38, '20070608 15:47:11.000', 1, NULL, NULL UNION ALL
SELECT 65, N'sfdfdfdsfdfsfsd', 1, N'test', N'N', 1, '20070614 16:53:59.000', 1, NULL, NULL UNION ALL
SELECT 69, N'crocin', 8, NULL, N'N', 1, '20080603 13:33:48.000', 1, NULL, NULL UNION ALL
SELECT 70, N'Paracetamol', 18, NULL, N'N', 1, '20080616 12:37:15.000', 1, NULL, NULL UNION ALL
SELECT 71, N'New Medi', 1, NULL, N'N', 38, '20080711 17:39:44.000', 1, NULL, NULL UNION ALL
SELECT 72, N'GaliNerve', 1, N's,k', N'N', 1, '20080804 09:39:18.000', 2, NULL, NULL UNION ALL
SELECT 75, N'Actiq12', 1, NULL, N'N', 228, '20091203 14:48:55.000', 1, NULL, NULL UNION ALL
SELECT 76, N'Actiq 123', 1, NULL, N'N', 288, '20091203 17:25:24.000', 1, NULL, NULL UNION ALL
SELECT 73, N'Volini Gel', 3, N'test des', N'N', 1, '20080801 16:36:33.000', 1, NULL, NULL UNION ALL
SELECT 77, N'Wrw', 1, NULL, N'N', 288, '20091205 19:26:39.000', 1, NULL, NULL UNION ALL
SELECT 80, N'Absorbine Junior', 1, NULL, N'N', 2, '20100804 16:51:15.000', 1, N'd04844', 1 UNION ALL
SELECT 74, N'Benzal', 1, N'For Acnes', N'N', 1, '20080807 17:35:09.000', 2, NULL, NULL UNION ALL
COMMIT;
RAISERROR (N'[dbo].[MEDICINES]: Insert Batch: 2.....Done!', 10, 1) WITH NOWAIT;
GO
SET IDENTITY_INSERT [dbo].[MEDICINES] OFF;
XML PART:
DECLARE @DOC_HNDL INT
DECLARE @PARAM1 VARCHAR(MAX)
DECLARE @PARAM2 INT,@PARAM3 INT
SET @PARAM1 = '
<CH>
<CM>
<MR SN="1" MN="d04112" MN_TXT="cabergoline" OCL="Y" DS="1" SF="0" SF_TXT="Other" SO="" MD="" SD="" ED="" PN="" PP="" PDN="0" RN="" RN_TXT="" PB="" DTN="-1" NoTablet="3" PRN="0" ICD_ID="0" ICD_TXT="" />
<MR SN="2" MN="d03668" MN_TXT="Absorbine Jr. Extra Strength" OCL="" DS="55" SF="10" SF_TXT="HS" SO="" QT="20" MD="joip" SD="2010" ED="2011" PN="123abc" PP="(556) 563-7687" PDN="0" RN="9" RN_TXT="Intramuscular (IM)" PB="" DTN="-1" NoTablet="" PRN="0" ICD_ID="0" ICD_TXT="" />
<MR SN="3" MN="70" MN_TXT="Paracetamol" OCL="" DS="" SF="0" SF_TXT="Other" SO="" MD="" SD="" ED="" PN="" PP="" PDN="0" RN="" RN_TXT="" PB="" DTN="-1" NoTablet="" PRN="0" ICD_ID="0" ICD_TXT="" />
</CM>
<CT CF="" />
<AD>
<AR SN="1" DT="5" DT_TXT="Recreational / Street Drugs" DS="a" QT="20" DN="" SD="" ED="" RN="" RN_TXT="" SF="0" SF_TXT="Other" SO="" DTN="-1" MD="" ICD_ID="0" ICD_TXT="" />
<AR SN="2" DT="6" DT_TXT="Other" DS="" QT="25" DN="" SD="" ED="" RN="" RN_TXT="" SF="0" SF_TXT="Other" SO="" DTN="-1" MD="" ICD_ID="0" ICD_TXT="" />
<AR SN="3" DT="8" DT_TXT="drug two" DS="" QT="20" DN="" SD="" ED="" RN="" RN_TXT="" SF="0" SF_TXT="Other" SO="" DTN="-1" MD="" ICD_ID="0" ICD_TXT="" />
<AR SN="4" DT="4" DT_TXT="Over The Counter (OTC)" DS="5" QT="5" DN="alt" SD="2009" ED="2011" RN="5" RN_TXT="Intranasal" SF="14" SF_TXT="Q2H1" SO="" DTN="-1" MD="no comment" ICD_ID="0" ICD_TXT="" />
</AD>
<PM>
<PR MN="Actiq" DS="5 ml" QT="60" MD="Indication23" SD="05/05/2007" ED="07/09/2010" PN="" PP="" SG="sig other" RN="" PB="Dr. Neena B.S. Jr." DCB="Dr. Harold Robbin" DTN="Allergy" NoTablet="#of tablet" PRN="0" ICD_ID="0" ICD_TXT="" />
<PR MN="crocin" DS="4" QT="1" MD="indication" SD="2007" ED="2008" PN="Pharmacy name" PP="( ) -" SG="Q4h" RN="Intrathecal / Intraventricular" PB="" DCB="" DTN="" NoTablet="41" PRN="0" ICD_ID="0" ICD_TXT="" />
</PM>
</CH>
'
SET @PARAM2 = 2
SET @PARAM3 = 1
EXEC SP_XML_PREPAREDOCUMENT @DOC_HNDL OUTPUT,@PARAM1
SELECT MN,LTRIM(RTRIM(REPLACE(MN_TXT,' ',' '))) MN_TXT INTO #MT_MEDICINE FROM OPENXML(@DOC_HNDL,'/CH/CM/MR',1) WITH (MN VARCHAR(10),MN_TXT VARCHAR(255))
--INSERT INTO DBO.MEDICINES(MEDICINE_NAME,MEDI_CATG_NUM,MEDICINE_COMM_TXT,STATUS_FLG,CREATED_BY,CREATED_DT,OFFICE_NUM,EXT_MED_NUM,EXT_MED_FLG)
SELECT MN_TXT,1,NULL,'N',@PARAM2,GETDATE(),@PARAM3,MN,1 FROM #MT_MEDICINE A WHERE NOT EXISTS(SELECT * FROM MEDICINES B WHERE A.MN<>B.EXT_MED_NUM AND CONVERT(VARCHAR(10),B.MEDICINE_NUM)<>A.MN)
EXEC SP_XML_REMOVEDOCUMENT @DOC_HNDL
DROP TABLE #MT_MEDICINE
I want to insert new medicine no. into medicine table with d04112,d03668 into new column ext_med_num in medicine table and also check the no. already exists(70(int)-paracetomol in this case) then skip it(dont insert) and insert only new.
I'm not able to check the condition properly as one column is int and another column is varchar.
can anyone help me out....
August 5, 2010 at 6:04 am
First question: are you working in SQL 2008? I ask because you posted in a SQL 2008 forum, yet your xml code looks like SQL 2000 era. If you're in 2005/2008, you really should be using the XML datatype.
The code I'd use for 2005/2008 is:
DECLARE @PARAM2 INT,
@PARAM3 INT,
@XML XML;
SET @XML = '
<CH>
<CM>
<MR SN="1" MN="d04112" MN_TXT="cabergoline" OCL="Y" DS="1" SF="0" SF_TXT="Other" SO="" MD="" SD="" ED="" PN="" PP="" PDN="0" RN="" RN_TXT="" PB="" DTN="-1" NoTablet="3" PRN="0" ICD_ID="0" ICD_TXT="" />
<MR SN="2" MN="d03668" MN_TXT="Absorbine Jr. Extra Strength" OCL="" DS="55" SF="10" SF_TXT="HS" SO="" QT="20" MD="joip" SD="2010" ED="2011" PN="123abc" PP="(556) 563-7687" PDN="0" RN="9" RN_TXT="Intramuscular (IM)" PB="" DTN="-1" NoTablet="" PRN="0" ICD_ID="0" ICD_TXT="" />
<MR SN="3" MN="70" MN_TXT="Paracetamol" OCL="" DS="" SF="0" SF_TXT="Other" SO="" MD="" SD="" ED="" PN="" PP="" PDN="0" RN="" RN_TXT="" PB="" DTN="-1" NoTablet="" PRN="0" ICD_ID="0" ICD_TXT="" />
</CM>
<CT CF="" />
<AD>
<AR SN="1" DT="5" DT_TXT="Recreational / Street Drugs" DS="a" QT="20" DN="" SD="" ED="" RN="" RN_TXT="" SF="0" SF_TXT="Other" SO="" DTN="-1" MD="" ICD_ID="0" ICD_TXT="" />
<AR SN="2" DT="6" DT_TXT="Other" DS="" QT="25" DN="" SD="" ED="" RN="" RN_TXT="" SF="0" SF_TXT="Other" SO="" DTN="-1" MD="" ICD_ID="0" ICD_TXT="" />
<AR SN="3" DT="8" DT_TXT="drug two" DS="" QT="20" DN="" SD="" ED="" RN="" RN_TXT="" SF="0" SF_TXT="Other" SO="" DTN="-1" MD="" ICD_ID="0" ICD_TXT="" />
<AR SN="4" DT="4" DT_TXT="Over The Counter (OTC)" DS="5" QT="5" DN="alt" SD="2009" ED="2011" RN="5" RN_TXT="Intranasal" SF="14" SF_TXT="Q2H1" SO="" DTN="-1" MD="no comment" ICD_ID="0" ICD_TXT="" />
</AD>
<PM>
<PR MN="Actiq" DS="5 ml" QT="60" MD="Indication23" SD="05/05/2007" ED="07/09/2010" PN="" PP="" SG="sig other" RN="" PB="Dr. Neena B.S. Jr." DCB="Dr. Harold Robbin" DTN="Allergy" NoTablet="#of tablet" PRN="0" ICD_ID="0" ICD_TXT="" />
<PR MN="crocin" DS="4" QT="1" MD="indication" SD="2007" ED="2008" PN="Pharmacy name" PP="( ) -" SG="Q4h" RN="Intrathecal / Intraventricular" PB="" DCB="" DTN="" NoTablet="41" PRN="0" ICD_ID="0" ICD_TXT="" />
</PM>
</CH>
';
SET @PARAM2 = 2;
SET @PARAM3 = 1;
WITH MT_MEDICINE (MN, MN_TXT) AS
(-- shred the XML
SELECT X.N.value('@MN', 'varchar(10)') ,
LTRIM(RTRIM(REPLACE(X.N.value('@MN_TXT', 'varchar(255)'), ' ', ' ')))
FROM @XML.nodes('/CH/CM/MR') AS X(N)
), CTE AS
(-- get just the valid numerics
SELECT MN, MN_TXT
FROM MT_MEDICINE
WHERE isnumeric(MN) = 1
)
--INSERT INTO DBO.MEDICINES(MEDICINE_NAME,MEDI_CATG_NUM,MEDICINE_COMM_TXT,STATUS_FLG,CREATED_BY,CREATED_DT,OFFICE_NUM,EXT_MED_NUM,EXT_MED_FLG)
-- get just valid numerics not already in the MEDICINE_NUM
SELECT A.MN_TXT,1,NULL,'N',@PARAM2,GETDATE(),@PARAM3,A.MN,1
FROM CTE A
LEFT JOIN MEDICINES B
ON convert(int, LTRIM(RTRIM(A.MN))) = B.MEDICINE_NUM
WHERE B.MEDICINE_NUM IS NULL
UNION ALL
-- get the others that aren't in the EXT_MED_NUM
SELECT A.MN_TXT,1,NULL,'N',@PARAM2,GETDATE(),@PARAM3,A.MN,1
FROM MT_MEDICINE A
LEFT JOIN MEDICINES B
ON A.MN = B.EXT_MED_NUM
WHERE B.EXT_MED_NUM IS NULL
AND isnumeric(A.MN) = 0;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 5, 2010 at 10:31 pm
Yes. I 'm using SQL server 2008. I'm just altering the existing code, so i am using the existing openxml and thanks for helping. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply