How can I do this without a cursor or loop?

Question

I hear to use Declarative 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

please do not tag your questions in the title. I have removed the SQL Server 2005 you added to your title and moved it to the tags that are at the bottom of your question. Thanks! — Abe Miessler
– Abe Miessler, Commented Oct 18, 2012 at 23:25

PyQL · Accepted Answer · 2012-10-18 23:34:30Z

2

UPDATE Class1 
SET Name = t.Name, 
Description = t.Description
FROM Class1 c 
JOIN TableA t ON c.id=t.id
WHERE t.name <> 'BAD NAME'

INSERT INTO Class1 (Class1Id, Name, Description)
select t.Id, t.Name, t.Description
FROM TableA t
LEFT JOIN Class1  c on t.id=c.id
where c.id IS NULL

edited Oct 18, 2012 at 23:34

answered Oct 18, 2012 at 23:17

PyQL

1,8303 gold badges18 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Preet Sangha Over a year ago

What about the TSQL Merge Stagement

user204588 Over a year ago

What about checking for if the record exists or not and updating if it does and inserting if it doesn't

Adam Wenger Over a year ago

@PreetSangha Merge is not available in SQL Server 2005

Abe Miessler Over a year ago

+1 - good answer despite not handling the update existing/insert new. @user204588, this gets you 90% of the way there. Shouldn't be too hard to take it the rest of the way. I'd recommend looking at Common table expressions and not in

PyQL Over a year ago

for update, JOIN will make sure that records exist in both tables and also the name is not bad, for insert it will insert records from TableA if they do not exist in Class1, is this what are you looking for?

|

Collectives™ on Stack Overflow

How can I do this without a cursor or loop?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related