Merging a result set with a table

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)

Merging a result set with a table

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-11-13 : 12:30:44

I have a table sectioninstructor
Sectionid,
Instructorid,
Instructorrole
-----------------------------------------------------------------------
Instructorrole (1 for primary instructor, 2 for secondary instructor)

Note I want to make sure that each section has only one primary instructor
i.e. if the new instructor is assigned primary role and there is already a record in the table that has the same sectionid and instructorrole =1 on inserting the new record I should change the instructorrole to 2
I will receive (a list of sectionids), instructorid, roleid I need to merge this list with the table sectioninstructor

I tried another way but I got an error message matched can’t have two updates
ALTER PROCEDURE [dbo].[usp_SectionInstructor_InsertList]
(
@SectionList nvarchar(1000),
@RoleID int,--(1 for primary and 2 for secondary)
@InstructorID int
)
AS
begin

select T.items as SectionID,
@InstructorID as instructorID,
@RoleID as InstructorROle

from
dbo.fn_SP2_Split(@SectionList,',') T

as Source

on
Target.SectionID=source.SectionID
and
Target.InstructorID=Source.SectionID

when Matched then
update
set Target.InstructorRole=@RoleID,
Target.updatedby=@updatedby

when not matched then
insert
(SectionID,
InstructorID,
InstructorRole
)
values
(Source.SectionID,
Source.InstructorID,
@RoleID
);

---the function

--splits a list to

CREATE FUNCTION [dbo].[fn_SP2_Split]
(@String nvarchar(max),
@Delimiter nchar(1)
)
returns @temptable TABLE (items nvarchar(max))
as
begin
declare @idx int
declare @slice nvarchar(250)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end

sarah

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-13 : 13:23:17

Hard to follow with broken code and unknown data types, but I think the logical order should be something like:

1. Begin transaction
2. If current insert/update is Primary then update all subordinate rows to NOT be Primary
3. Do insert/update
4. Commit transaction

Nadermfr
Starting Member

4 Posts

Posted - 2012-11-13 : 15:04:25

can't I use merge

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-13 : 15:37:18

And how is that working for you?

Also, do have a constraint on your table to prevent more that one primary?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources