insert multiple row copies for lookup without cursor

21 Jul 2006 9:58 AM

adam

I'm after some help with an insert that uses a select cursor that
probably should not.

The schema if for an existing application and cannot be altered. I do
not have access to the source for this application

[Table: Forms]
FormId (int identity) Name (varchar)
1 Apple
2 Banana
3 Cherry
4 Date

[Table: FormFields]
FormFieldId(int identity) FormId (int) Name (varchar) Other(various)
1 1 Name ####
2 1 Age ####
3 1 Phone ####
4 2 Name ####
5 3 Age ####
6 4 Phone ####
.... (repeat; Name, Age,Phone for all forms)

Other represents all the other fields in the FormFields table

I need to add new fields on demand one for each Form so I did this [1]
but I've been told in the past that if you are using a cursor and you
are not an expert (I just about qualify as beginner) then it's probably
wrong.

Any ideas?

adam

[1]
declare @FormId int
declare curForms Cursor For
    select FormId from Forms

open curForms
fetch next from curForms into @FormId

while @@FETCH_STATUS = 0
begin

     insert into FormFields
       (FormId, Name, Other)
     Values
       (@FormId, 'my new field', ####)

     fetch next from curForms into @FormId
end

close curForms
deallocate curForms

21 Jul 2006 10:41 AM

ML

insert FormFields
(
FormId, Name, Other
)
select FormId
,'my new field' as Name
,Other

ML

---
http://milambda.blogspot.com/

21 Jul 2006 12:14 PM

adam

excellent. I was right about being wrong :-)

thanks

ML wrote:
Show quote

> insert FormFields
>   (
>   FormId, Name, Other
>   )
>   select FormId
>            ,'my new field' as Name
>            ,Other

21 Jul 2006 12:24 PM

ML

You were also right to come here. ;)

ML

---
http://milambda.blogspot.com/