I'm looking for a simpler way to do this as I'm clearly thinking like a developer instead of a DBA. How would a DBA tackle this?
I have a table with 3 column: PK, Name, FK.
PK Name FK
1 someNameA 1
2 log 1
3 someNameB 2
I want to say if a foreign key of n does not have "log" for a name value then add it to the table.
I think I'm heading down a dark road as this seems rather vebose:
declare @ids table (ids varchar(8))
declare @log_ids table (ids varchar(8))
declare @nolog_ids table (ids varchar(8))
insert into @ids
select fk
from table
where fk is not null
insert into @log_ids
select fk
from table
where fk is not null
and name like 'log%'
insert into @nolog_ids
select distinct ids
from @ids ids
where ids.ids not in (select ids from @log_ids)
while ((select count(1) from @nolog_ids) > 0)
begin
declare @currentid int
set @currentid = (select top 1 ids from @nolog_ids)
insert into table (name, fk)
select 1, 'log', @currentid
delete from @nolog_ids where ids = @currentid
end