SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Merging a result set with a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sarahmfr
Posting Yak Master

214 Posts

Posted - 11/13/2012 :  12:30:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 11/13/2012 :  13:23:17  Show Profile  Reply with Quote
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

Edited by - Lamprey on 11/13/2012 13:23:44
Go to Top of Page

Nadermfr
Starting Member

4 Posts

Posted - 11/13/2012 :  15:04:25  Show Profile  Reply with Quote
can't I use merge
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 11/13/2012 :  15:37:18  Show Profile  Reply with Quote
And how is that working for you?

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

Edited by - Lamprey on 11/13/2012 15:41:51
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000