Insert new row in table for each distinct value in another 2

[mirirom]

hi,

i'm hoping someone can offer some advice as to how to go about dynamic row insertion. i have a table filled with projects, & another table with associated phases (subprojects). there's a business rule that states that all projects *must* have a base phase - '.000', and unfortunatley, none of the projects (at time of import) in the database do.

e.g. Project Phase
98008 010
98008 020
etc

what i'd like to do is create a base phase record for each distinct project number in the projects table. i'm thinking that i should itterate through a resultset of distinct project values and simply do an insert in the phases table. however, quite a few posts in here have stated that cursors should be used as last resort. i also made the mistake of assuming that triggers would execute for each row insertion (in my case, i wanted the triggers to create the phase record when projects were imported during a batch process).

any thoughts on this? there's got to be a simple way to do this. many thanks in advance!



..:: mirirom ::..

 

[nigelrivett]

insert tbl
select distinct Project, '000'
from tbl t1
where not exists ( select * from tbl t2 where t1.project = t2.project and t2.Phase = '000')


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[vongrunt]

Or maybe even:

insert tbl
select distinct Project, '000'
from tbl t1
where t1.Phase <> '000'

DISTINCT removes duplicates, '000' is a constant, so this may work.

 

[mirirom]

thanks guys,

both solutions suffice. much appreciated!

..:: mirirom ::..