asked
How can I do this without a cursor or loop?
I hear to use set based programming but I have no idea how to do something like this in sql server 2005 (I typed this up really fast so I'm not sure if all the syntax is correct but I think you'll understand what I'm looking for) declare curs cursor for select @Name, @Description, @Id FROM TableA open curs while(1=1) begin fetch next from curs into @Name, @Description, @Id if(@@fetch_status<>0) break set @recordCount = (SELECT COUNT(*) As RecordCount FROM Class1 WHERE Class1Id = @Id) if(@recordCount > 0) begin if(@Name = 'BAD NAME') CONTINUE UPDATE Class1 SET Name = @Name , Description = @Description WHERE Class1Id = @Id end else begin INSERT INTO Class1 (Class1Id, Name, Description) VALUES (@Id, @Name, @Description) end end close curs deallocate curs
So if i've read it correctly if the Id from TableA equals the ClassId in Class1 and the Name in TableA isn't 'BAD NAME' you want to update that Id in Class1. If the Id from TableA doesn't exist in Class1 then you want to insert it? So this will update matching Ids where name isn't 'BAD NAME' and update the name and description: UPDATE b SET Name = @Name, Description = @Description FROM TableA a INNER JOIN Class1 b ON
a.Id = Class1Id WHERE a.NAME != 'BAD NAME' and this will insert record not already in Class1: INSERT INTO Class1 SELECT Id, Name, Description FROM TableA a LEFT JOIN Class1 b ON
a.Id = Class1Id WHERE b.ClassId IS NULL AND a.NAME != 'BAD NAME'
I think that you could do something like this: -- Update UPDATE class SET Name = t.@NAME , [DESCRIPTION] = t.@DESCRIPTION FROM Class1 class INNER JOIN TableA t ON class.Class1Id = t.@ID WHERE t.@Name = 'BAD NAME' GO -- Insert INSERT Class1(Class1Id, Name, Description) SELECT @ID, @Name, @Description FROM TableA WHERE @Name 'BAD NAME' GO As always, test this first! Hope this helps!
Hi again John, basically what you're doing is an UPSERT. in the absence of the merge statement as your using 2005 you'll have to do two separate statements, something akin to: Update C set C.Name = A.Name, C.Description = A.Description from Class1 as C Join TableA as A ON
C.ID =
A.ID and A.Name = 'Bad Name' go insert into class1(ClassID, Name, Description) select
A.ID, A.Name, A.Description from TableA as A Join Class1 as C on
A.ID =
C.ID WHERE A.Name <> 'Bad Name' Hope this helps
11 People are following this question.
