0

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
0

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.