Can this be done without a cursor? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2008 Forums

Transact-SQL (2008)

Can this be done without a cursor?

Author

Topic

LaurieCox

158 Posts

Posted - 2015-04-09 : 09:01:05

I have four tables (complete data definition and test data at end of post):

kt_ClientMedications

kt_ClientMedicationInstructions (foreign key ClientMedicationId)

kt_ClientMedicationScriptDrugs (foreign key ClientMedicationInstructionId)

kt_ClientMedicationScripts

I need to insert a copy of all rows in kt_ClientMedicationInstructions with a given ClientMedicationsId into kt_ClientMediationsInstructions. I then need to update the ClientMedicationInsturctionId in kt_ClientMedicationScriptDrugs table to point to the correct new instruction record(s).

If there is only one record that I need to copy this would be simple:


declare @ClientMedicationId int = 72558
declare @ClientMedicationInsturctionId int

set @ClientMedicationInsturctionId = 
    (select ClientMedicationInstructionId 
       from kt_ClientMedicationInstructions
      where ClientMedicationId = @ClientMedicationId)

insert into kt_ClientMedicationInstructions
select ClientMedicationId
	 , MedicationDescription
	 , Active
	 , Schedule
  from kt_ClientMedicationInstructions 
 where ClientMedicationInstructionId  = @ClientMedicationInsturctionId
 
update kt_ClientMedicationScriptDrugs
   set ClientMedicationInstructionId = @@IDENTITY
 where ClientMedicationInstructionId = @ClientMedicationInsturctionId

But there can be multiple kt_ClientMedicaitonInstructions records. I thought of using the OUTPUT clause on the insert to return the new ids into a table variable. But I couldn't figure out how to associate each new record's ClientMedicaitonInstructionId with the originating record's ClientMedicaitonInstructionId when I went to update the records in kt_ClientMedicationScriptDrugs.

I ended up writing a cursor to process one insert at a time (see code at end of post). We are only talking about 1 to 4 records so this is really not a performance problem. But I want to learn and wondered if there was any way to do this without a cursor.

Note: I also change the Active flag on the original ClientMedicationInstructions record to N and change the ScriptEventType on the ClientMedicationScripts table to C.

Thanks,

Laurie

Test tables:


-- kt_ClientMedications
CREATE TABLE [dbo].[kt_ClientMedications](
	[ClientMedicationId] [int] IDENTITY(1,1) NOT NULL,
	[MedicationNameId] [int] NOT NULL,
 CONSTRAINT [kt_ClientMedications_PK] PRIMARY KEY 
(
	[ClientMedicationId] ASC
))

-- kt_ClientMedicationInstructions
CREATE TABLE [dbo].[kt_ClientMedicationInstructions](
	[ClientMedicationInstructionId] [int] IDENTITY(1,1) NOT NULL,
	[ClientMedicationId] [int] NOT NULL,
	[MedicationDescription] [varchar](100) NULL,
	[Active]  [char] (1) null,
	[Schedule] [int] null,
 CONSTRAINT [kt_ClientMedicationInstructions_PK] PRIMARY KEY 
(
	[ClientMedicationInstructionId] ASC
))

ALTER TABLE [dbo].[kt_ClientMedicationInstructions]  
 WITH NOCHECK ADD  
 CONSTRAINT [kt_ClientMedications_ClientMedicationInstructions_FK] 
 FOREIGN KEY([ClientMedicationId])
REFERENCES [dbo].[kt_ClientMedications] ([ClientMedicationId])

ALTER TABLE [dbo].[kt_ClientMedicationInstructions] 
CHECK CONSTRAINT [kt_ClientMedications_ClientMedicationInstructions_FK]

-- kt_ClientMedicationScripts
CREATE TABLE [dbo].[kt_ClientMedicationScripts](
	[ClientMedicationScriptId] [int] IDENTITY(1,1) NOT NULL,
	[ScriptEventType] [char](1) NOT NULL,
 CONSTRAINT [kt_ClientMedicationScripts_PK] PRIMARY KEY 
(
	[ClientMedicationScriptId] ASC
))

-- kt_ClientMedicationScriptDrugs
CREATE TABLE [dbo].[kt_ClientMedicationScriptDrugs](
	[ClientMedicationScriptDrugId] [int] IDENTITY(1,1) NOT NULL,
	[ClientMedicationScriptId] [int] NULL,
	[ClientMedicationInstructionId] [int] NOT NULL,
	[Pharmacy] [decimal](10, 2) NULL,
 CONSTRAINT [kt_ClientMedicationScriptDrugs_PK] PRIMARY KEY 
(
	[ClientMedicationScriptDrugId] ASC
))


ALTER TABLE [dbo].[kt_ClientMedicationScriptDrugs]  
WITH NOCHECK ADD  
CONSTRAINT [kt_ClientMedicationInstructions_ClientMedicationScriptDrugs_FK] 
FOREIGN KEY([ClientMedicationInstructionId])
REFERENCES [dbo].[kt_ClientMedicationInstructions] ([ClientMedicationInstructionId])

ALTER TABLE [dbo].[kt_ClientMedicationScriptDrugs] 
CHECK CONSTRAINT [kt_ClientMedicationInstructions_ClientMedicationScriptDrugs_FK]

ALTER TABLE [dbo].[kt_ClientMedicationScriptDrugs]  
 WITH NOCHECK ADD  
 CONSTRAINT [kt_ClientMedicationScripts_ClientMedicationScriptDrugs_FK] 
 FOREIGN KEY([ClientMedicationScriptId])
REFERENCES [dbo].[kt_ClientMedicationScripts] ([ClientMedicationScriptId])

ALTER TABLE [dbo].[kt_ClientMedicationScriptDrugs] 
CHECK CONSTRAINT [kt_ClientMedicationScripts_ClientMedicationScriptDrugs_FK]

--Load Test Data
SET IDENTITY_INSERT kt_ClientMedications on

insert into kt_ClientMedications
(ClientMedicationId, MedicationNameId)
select 34749, 278 union all
select 71717, 55814 union all
select 72558, 20865 
 
SET IDENTITY_INSERT kt_ClientMedications off

SET IDENTITY_INSERT kt_ClientMedicationInstructions on

insert into kt_ClientMedicationInstructions
(ClientMedicationInstructionId, ClientMedicationId, 
 MedicationDescription, Active, Schedule)
select 71514, 34749, 'Cogentin 0.5 mg tablet', 'Y', 4861 union all
select 139070, 71717, 'clonidine HCl 0.3 mg tablet', 'Y', 4863 union all
select 139071, 71717, 'clonidine HCl 0.3 mg tablet', 'Y', 4864 union all
select 140145, 72558, 'Focalin 5 mg tablet', 'Y', 4861 union all
select 140146, 72558, 'Focalin 5 mg tablet', 'Y', 10831
 
SET IDENTITY_INSERT kt_ClientMedicationInstructions off

SET IDENTITY_INSERT kt_ClientMedicationScripts on

insert into kt_ClientMedicationScripts
(ClientMedicationScriptId, ScriptEventType)
Select 164828, 'R' union all
Select 266592, 'R' union all
Select 270626, 'R' 

SET IDENTITY_INSERT kt_ClientMedicationScripts off

SET IDENTITY_INSERT kt_ClientMedicationScriptDrugs on

insert into kt_ClientMedicationScriptDrugs
(ClientMedicationScriptDrugId, ClientMedicationScriptId, 
 ClientMedicationInstructionId, Pharmacy)
select 202259, NULL, 71514, 0 union all
select 202441, 164828, 71514, 30 union all
select 364566, NULL, 139070, 0 union all
select 364567, NULL, 139071, 0 union all
select 364614, 266592, 139070, 30 union all
select 364615, 266592, 139071, 15 union all
select 366954, NULL, 140145, 0 union all
select 366955, NULL, 140146, 0 union all
select 372083, 270626, 140145, 30 union all
select 372084, 270626, 140146, 330 

SET IDENTITY_INSERT kt_ClientMedicationScriptDrugs off

Cursor Solution:


declare @ClientMedicationId int = 71717

declare @DataToProcess table 
          (ClientMedicationScriptDrugId int,
           ClientMedicationInstructionId int,
           ClientMedicationScriptId int)
           
           
insert into @DataToProcess
select cmsd.ClientMedicationScriptDrugId
     , cmi.ClientMedicationInstructionId
     , cmsd.ClientMedicationScriptId
  from kt_ClientMedications cm 
           join kt_ClientMedicationInstructions cmi 
                on cm.ClientMedicationId = cmi.ClientMedicationId
           join kt_ClientMedicationScriptDrugs cmsd 
                on cmsd.ClientMedicationInstructionId = cmi.ClientMedicationInstructionId
               and cmsd.ClientMedicationScriptId is not null
 where cm.ClientMedicationId = @ClientMedicationId
 
SELECT * FROM @DataToProcess
 
 declare cur cursor
 read_only
 for 
    Select ClientMedicationScriptDrugId
         , ClientMedicationInstructionId
         , ClientMedicationScriptId
      from @DataToProcess
      
declare @ClientMedicationScriptDrugId int
declare @ClientMedicationInstructionId int
declare @ClientMedicationScriptId int

OPEN cur

	FETCH NEXT FROM cur 
	 INTO @ClientMedicationScriptDrugId
		, @ClientMedicationInstructionId
		, @ClientMedicationScriptId 
	    
	WHILE @@FETCH_STATUS = 0
	BEGIN
		insert into kt_ClientMedicationInstructions
		select ClientMedicationId
			 , MedicationDescription
			 , Active
			 , Schedule
			 --, ClientMedicationInstructionId
		  from kt_ClientMedicationInstructions
		 where ClientMedicationInstructionId = @ClientMedicationInstructionId
		 
		update kt_ClientMedicationScriptDrugs
		   set ClientMedicationInstructionId = @@IDENTITY
		 where ClientMedicationScriptDrugId = @ClientMedicationScriptDrugId

		update kt_ClientMedicationInstructions 
		   set Active = 'N'
		 where ClientMedicationInstructionId = @ClientMedicationInstructionId

		update kt_ClientMedicationScripts
		   set ScriptEventType = 'C'
		 where ClientMedicationScriptId = @ClientMedicationScriptId

		FETCH NEXT FROM cur 
		 INTO @ClientMedicationScriptDrugId
			, @ClientMedicationInstructionId
			, @ClientMedicationScriptId 
	END      

CLOSE cur
DEALLOCATE cur

Expected output @ClientMedicationId = 71717


kt_ClientMedicationsInstructions Before:
ClientMedicationInstructionId ClientMedicationId                                Active Schedule
----------------------------- ------------------ ------------------------------ ------ -----------
139070                        71717              clonidine HCl 0.3 mg tablet    Y      4863
139071                        71717              clonidine HCl 0.3 mg tablet    Y      4864

kt_ClientMedicationsInstructions After:
ClientMedicationInstructionId ClientMedicationId                                Active Schedule
----------------------------- ------------------ ------------------------------ ------ -----------
139070                        71717              clonidine HCl 0.3 mg tablet    N      4863
139071                        71717              clonidine HCl 0.3 mg tablet    N      4864
140147                        71717              clonidine HCl 0.3 mg tablet    Y      4863
140148                        71717              clonidine HCl 0.3 mg tablet    Y      4864

kt_ClientMedicationScriptDrugs Before
ClientMedicationScriptDrugId ClientMedicationScriptId ClientMedicationInstructionId Pharmacy
---------------------------- ------------------------ ----------------------------- ----------
364566                       NULL                     139070                        0.00
364567                       NULL                     139071                        0.00
364614                       266592                   139070                        30.00
364615                       266592                   139071                        15.00


kt_ClientMedicationScriptDrugs After:
ClientMedicationScriptDrugId ClientMedicationScriptId ClientMedicationInstructionId Pharmacy
---------------------------- ------------------------ ----------------------------- ----------
364566                       NULL                     139070                        0.00
364567                       NULL                     139071                        0.00
364614                       266592                   140147                        30.00
364615                       266592                   140148                        15.00

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-09 : 13:21:11

Something similar to this

-- Decide which ClientMedicationID to work with;
DECLARE	@ClientMedicationID INT = 71717;

-- Create a temporary staging table
CREATE TABLE	#Stage
		(
			ClientMedicationInstructionID INT NOT NULL,
			ClientMedicationScriptDrugID INT NOT NULL,
			ClientMedicationScriptID INT NOT NULL
		);

-- Do the MERGE
MERGE	dbo.kt_ClientMedicationInstructions AS tgt
USING	(
		SELECT		cmi.ClientMedicationID,
				cmi.MedicationDescription,
				'N' AS Active,
				cmi.Schedule,
				cmsd.ClientMedicationScriptDrugID,
				cmsd.ClientMedicationScriptID
		FROM		dbo.kt_ClientMedications AS cm
		INNER JOIN	dbo.kt_ClientMedicationInstructions AS cmi ON cmi.ClientMedicationID = cm.ClientMedicationID
		INNER JOIN	dbo.kt_ClientMedicationScriptDrugs AS cmsd ON cmsd.ClientMedicationInstructionID = cmi.ClientMedicationInstructionID
		WHERE		cm.ClientMedicationId = @ClientMedicationID
	) AS src ON 1 = 2
WHEN	NOT MATCHED BY TARGET
	THEN	INSERT	(
				ClientMedicationID,
				MedicationDescription,
				Active,
				Schedule
			)
		VALUES	(
				src.ClientMedicationID,
				src.MedicationDescription,
				src.Active,
				src.Schedule
			)
OUTPUT	inserted.ClientMedicationInstructionID,
	src.ClientMedicationScriptDrugID,
	src.ClientMedicationScriptID
INTO	#Stage
	(
		ClientMedicationInstructionID,
		ClientMedicationScriptDrugID,
		ClientMedicationScriptID
	);

UPDATE		w
SET		w.ClientMedicationInstructionID = s.ClientMedicationInstructionID
FROM		dbo.kt_ClientMedicationScriptDrugs AS w
INNER JOIN	#Stage AS s ON s.ClientMedicationScriptDrugID = w.ClientMedicationScriptDrugID;
		 
UPDATE		w
SET		w.ScriptEventType = 'C'
FROM		dbo.kt_ClientMedicationScripts AS w
INNER JOIN	#Stage AS s ON s.ClientMedicationScriptId = w.ClientMedicationScriptID;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

LaurieCox

158 Posts

Posted - 2015-04-09 : 13:43:35

Thank you.

I should of thought of the merge. I have used the merge statement and the output clause on the merge statement to output to log files and I still didn't think about it for this problem.

So you just join to the other tables in order to have the information when updating the second table.

I like this a lot more than the cursor.

Laurie

LaurieCox

158 Posts

Posted - 2015-04-10 : 13:52:07

Well this is sad. I got the Merge statement to work against my testing tables. Worked like a charm. I then switched it to go against the actual tables in our test environment (that mirrors production) and got this error:

The target table 'tgt' of the MERGE statement cannot have any enabled rules. Found rule 'rule_YOrN'.

So I guess I am back to the cursor solution. Damn I really liked the merge.

Thank you SwePeso for the solution anyway. It has added to my knowledge and there may be places where I can use it in the future.

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources