Problems with cursor with null values

Question

Problems with cursor with null values

Jorge Olimpia

SSC Enthusiast

Points: 133
More actions
March 8, 2005 at 2:01 pm

#164805

Hi friends,
I have a cursor in SQL Server 2000 that make a insert command from table_from to table_to. It´s around this:
table_to -> colunms A, B, C
table_from:
a     b     c
1     1     null
2     2     2
3     null  3
--Start cursor
declare @a int, @b-2 int, @C int, @to varchar(100)
set @a = ''
set @b-2 = ''
set @C = ''
set @to = ''
declare runinsert cursor for
select a, b, c from table_from
open runinsert
fetch next from runinsert from @a, @b-2, @C
while @@fetch_status = 0
begin
   set @to = 'insert into table_to(A, B, C) values('''+(select @a)+''','''+(select @b-2)+''','''+(select @C)''')'
   exec(@to)
   fetch next from runinsert from @a, @b-2, @C
end
close runinsert
deallocate runinsert
--Finish cursor
When I run this cursor only 1 row is affected because the others 2 rows have a null value. I need to insert the 3 rows but I don´t know how to make this.
Thanks for help! 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

The Nulls are the least of your problems. Taking a perfectly simple problem that cries out for a set-based solution, and throwing cursors & dynamic SQL at it is an order of magnitude bigger problem.

1 simple SQL statement, no cursor, no variables, no problems:

insert into table_to

(A, B, C)

Select

a,

IsNull(b, ''),

IsNull(c, '')

From

table_from

noeld SSC Guru Points: 96590 More actions · Answer 2

And probaly you don't even need the IsNull checking that PW wrote

insert into table_to

(A, B, C)

Select

a, b, c

From

table_from

* Noel

Jorge Olimpia SSC Enthusiast Points: 133 More actions · Answer 3

All right friends, but I forget one detail:

Before to apply the insert I make the following command:

if not exists(select a from table_from where a = ''+(select @a)+''))

begin

--make insert

end

else

begin

update table_from

set b = @b-2 where a = @a

update table_from

set c = @C where a = @a

end

It´s for this that I used a cursor.

noeld SSC Guru Points: 96590 More actions · Answer 4

So what?,

Update D Set a= S.a, b = S.b, C = S.c

From table_to D join table_from S on D.a =S.a

insert into table_to

(A, B, C)

Select

a, b, c

From

table_from S join table_to D on S.a = D.a where S.a is null

* Noel