Re: Cursors (again)

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 11/08/04 

Date: Mon, 8 Nov 2004 17:09:45 -0600

What does it matter if TableB is temporary? The code will work no matter
what the values in tableB, or tableA.

Unless I am missing something, here is a cursor that does the same thing
(note I change the table column names to match what was in your original
post.)

set nocount on
go
drop table tableA
drop table tableB
go
create table tableA
(
 code char primary key,
 description varchar(10)
)
insert into tableA
values ('A','ALPHA')
insert into tableA
values ('B','BRAVO')
insert into tableA
values ('C','ZETA')
insert into tableA
values ('D','DELTA')
insert into tableA
values ('G','GOLF')

create table tableB
(
 code char primary key,
 description varchar(10)
)
insert into tableB
values ('A','ALPHA')
insert into tableB
values ('B','BRAVO')
insert into tableB
values ('C','CHARLIE')
insert into tableB
values ('D','DELTA')
insert into tableB
values ('E','ECHO')
insert into tableB
values ('F','FOXTROT')

go

declare @code as varchar(10), @description as varchar(50)

declare cTableB cursor for
  select Code, Description from TableB
  for read only

open cTableB
fetch next from cTableB into @code, @description

while (@@fetch_status = 0)
 begin
 if not exists ( select *
          from tableA
      where code = @code)
  begin
           insert into TableA (Code,Description) Values (@code,
@description)
           print 'Inserting'
  end
 else if exists (select *
          from tableA
      where code = @code
    and description <> @description)
         begin
           update TableA set Description = @description where Code = @code
           Print 'Updating'
         end

 fetch next from cTableB into @code, @description

 end

close cTableB
deallocate cTableB

select * from tableA

Exectuting this returns:

Updating
Inserting
Inserting
code description
---- -----------
A ALPHA
B BRAVO
C CHARLIE
D DELTA
E ECHO
F FOXTROT
G GOLF 

-- 
----------------------------------------------------------------------------
Louis Davidson - drsql@hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design - 
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in 
consulting services.  All other replies may be ignored :)
"Billy" <Billy@discussions.microsoft.com> wrote in message 
news:BBC93700-F3E9-4853-A2BD-2EFD2067FD4F@microsoft.com...
> No. TableA has values in it (lets say pricing values). I receive an xl
> spread*** with new codes and updated codes. I insert these into TableB.
>
> I then want to run through TablB checking each record to see if it's in
> TableA. If it is, then the record is updated, otherwise, a new record is
> inserted into TableA using the data in TableB.
>
> TableB is only temporary. It will be re-populated when I next get my xl
> spread***. I'm using this procedure as a way of keeping TableA up to 
> date.
>
> Thx for response though.
>
> STILL NEED HELP! ;-)
>
> Billy
>
> "Louis Davidson" wrote:
>
>> Why use a cursor?
>>
>> create table tableA
>> (
>>  tableAkey char primary key,
>>  value varchar(10)
>> )
>> insert into tableA
>> values ('A','ALPHA')
>> insert into tableA
>> values ('B','BRAVO')
>> insert into tableA
>> values ('C','ZETA')
>> insert into tableA
>> values ('D','DELTA')
>> insert into tableA
>> values ('G','GOLF')
>>
>> create table tableB
>> (
>>  tableBkey char primary key,
>>  value varchar(10)
>> )
>> insert into tableB
>> values ('A','ALPHA')
>> insert into tableB
>> values ('B','BRAVO')
>> insert into tableB
>> values ('C','CHARLIE')
>> insert into tableB
>> values ('D','DELTA')
>> insert into tableB
>> values ('E','ECHO')
>> insert into tableB
>> values ('F','FOXTROT')
>>
>> go
>>
>>
>> --new row creation
>> Insert  into tableA
>> select  tableBKey, value
>> from    tableB
>>                             --rows that arent already in tableA
>> where   not exists (select *
>>                             from tableA
>>                             where tableB.tableBkey = tableA.tableAkey)
>>
>> update  tableA
>> set     value = tableB.value
>> from    tableA
>>                 join tableB
>>                         on tableA.tableAkey = tableB.tableBkey --the key 
>> IS
>> in tableA
>>                              and tableA.value <> tableB.value --but the
>> value doesnt match
>>
>> select * from tableA
>>
>> tableAkey value
>> --------- ---------- 
>> A         ALPHA
>> B         BRAVO
>> C         CHARLIE
>> D         DELTA
>> E         ECHO
>> F         FOXTROT
>> G         GOLF
>>
>>
>>
>>
>>
>>
>> -- 
>> ----------------------------------------------------------------------------
>> Louis Davidson - drsql@hotmail.com
>> SQL Server MVP
>>
>> Compass Technology Management - www.compass.net
>> Pro SQL Server 2000 Database Design -
>> http://www.apress.com/book/bookDisplay.html?bID=266
>> Note: Please reply to the newsgroups only unless you are interested in
>> consulting services.  All other replies may be ignored :)
>>
>> "Billy" <Billy@discussions.microsoft.com> wrote in message
>> news:C1848722-5CB7-40BB-8316-B001DD5EDAFB@microsoft.com...
>> >I have the following T_SQL working (kind of). What it's doing now is
>> > completely ignoring the insert statement, which means the @@cursor_rows
>> > never
>> > = 0.
>> >
>> > I have 5 records in TableA
>> > A - ALPHA
>> > B - BRAVO
>> > C - ZETA
>> > D - DELTA
>> > G - GOLF
>> >
>> > and 6 records in TableB
>> > A - ALPHA
>> > B - BRAVO
>> > C - CHARLIE
>> > D - DELTA
>> > E - ECHO
>> > F - FOXTROT
>> >
>> > Basically, TableA should have C updated to CHARLIE and have E and F
>> > inserted
>> > - but only C gets updated. E and F are not inserted.
>> >
>> > Any help appreciated.
>> >
>> > Thx
>> >
>> > (Tom - I did try your solution, but it didn't work and, seeing as I 
>> > wasn't
>> > quite sure what it was doing (new to this) I didn't spend much time 
>> > trying
>> > to
>> > figure it out seeing as I presumed (incorrectly!) that I was almost
>> > there!)
>> >
>> > ---------------------------------------------
>> > declare @code as varchar(10), @description as varchar(50)
>> >
>> > declare cTableB cursor for
>> >  select Code, Description from TableB
>> >  for read only
>> >
>> > open cTableB
>> > fetch next from cTableB into @code, @description
>> >
>> > while (@@fetch_status = 0)
>> >  begin
>> >    declare cTableA cursor for
>> >      select Code from TableA where Code = @code
>> >      for read only
>> >
>> >    open cTableA
>> >    fetch next from cTableA
>> >
>> >    begin
>> >      if (@@cursor_rows = 0)
>> >        begin
>> >          insert into TableA (Code,Description) Values (@code,
>> > @description)
>> >          print "Inserting"
>> >        end
>> >      else
>> >        begin
>> >          update TableA set Description = @description where Code = 
>> > @code
>> >          Print "Updating"
>> >        end
>> >    end
>> >    close cTableA
>> >    deallocate cTableA
>> >    fetch next from cTableB into @code, @description
>> >  end
>> >
>> > close cTableB
>> > deallocate cTableB
>> > --------------------------------------------------------- 
>>
>>
>>