create type keyword_list
as table (
    keyword varchar(300)
);

go

create table keyword (
    id int identity(1,1),
    keyword varchar(300) not null,
    language_id bigint not null,
    location_id bigint not null,
    last_updated date,
    constraint pk_keyword primary key nonclustered (id),
    constraint uc_keyword unique clustered (keyword, language_id, location_id)
);

create table keyword_rank (
    id int identity(1,1) primary key,
    search_volume bigint not null,
    average_cpc bigint not null,
    created_at date not null,
    keyword_id int not null,
    constraint fk_keyword foreign key (keyword_id) references keyword(id)
);

go

create or alter procedure create_keywords
    @list as keyword_list readonly,
    @language_id as bigint,
    @location_id as bigint
as
begin
    insert into keyword (keyword, language_id, location_id)
    select mk.keyword, @language_id, @location_id
    from (
        select distinct(mk.keyword)
        from @list mk
        where mk.keyword not in (
            select k.keyword
            from keyword k
            where k.language_id = @language_id
            and k.location_id = @location_id
            and k.keyword in (select l.keyword from @list l)
        )
    ) as mk
end;