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, Oct 18 '12 at 23:25

PyQL · Accepted Answer · 2012-10-18T23:34:30.183

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 '12 at 23:34

answered Oct 18 '12 at 23:17

PyQL

1,830
3
18
22

What about the TSQL Merge Stagement – Preet Sangha Oct 18 '12 at 23:22
What about checking for if the record exists or not and updating if it does and inserting if it doesn't – user204588 Oct 18 '12 at 23:24
@PreetSangha Merge is not available in SQL Server 2005 – Adam Wenger Oct 18 '12 at 23:27
+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` – Abe Miessler Oct 18 '12 at 23:28
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? – PyQL Oct 18 '12 at 23:30
I kind of disagree though, the handling of the update/insert new is the biggest issue. It's easy to do an insert and update on their own. I'm not sure how to write this and that is the problem. – user204588 Oct 18 '12 at 23:31
Hmm @Bader, you may be right. I'm thinking that may work and it is easier then I thought. – user204588 Oct 18 '12 at 23:43
The only problem here is I think I made the example too simple. I also need to take the new identity id from the insert and use it in another query – user204588 Oct 19 '12 at 00:54

How can I do this without a cursor or loop?

1 Answers1