Sql-server – Insert where not already available for FK

sql server

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

Best Answer

You can just insert into table where fk not in the criteria you specified. This all can be a single insert statement. No need to do RBAR.

It sounds like you're looking to insert where you have fk records that do not have a corresponding "log" record, then insert.

insert into table
select distinct name, fk
from table t
where fk not in (
  select distinct fk
  from table t1
  where name like 'log%'
    and t1.fk is not null)

This is really crude but... just want to make sure I understood you correctly.

Also, in the future, if you do need to do a RBAR operation...

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

I prefer to use this instead... more readable and potentially faster.

declare @currentid int

while exists (select * from @nolog_ids)
begin

    select top 1 @currentid = ids from @nolog_ids

    insert into table (name, fk)
    select 1, 'log', @currentid

    delete from @nolog_ids where ids = @currentid

end