Sql-server – Insert data in relation table and into data table with less query cost

insertsql-server-2005

I have a similar table layout:

create table data ( id int identity primary key
                  , value int not null);

create table relation (id int identity primary key
                      , someid int not null
                      , dataid int foreign key references data(id));

When inserting data into the tables, in most of the cases I need to insert something in data and also in relation.

Currently I do it like this:

-- insert data.value = 1337 and relation.someid = 42 + relation.dataid = 1337
if not exists (select id from data where value = 1337) 
    insert into data (value) 
    values (1337);
    insert into relation (someid, dataid) 
    values (42, (select id from data where value = 1337));

Is this the proper solution?

What can I do if I need to insert many of these lines?

Currently I insert all data into data and then do the queries for relation.

Best Answer

Use the OUTPUT clause of the INSERT statement to capture the identity values created in data. Then use that to write to relation.

create table #data ( id int  primary key
                  , value int not null);


if not exists (select id from data where value = 1337) 
    insert into data (value) 
    OUTPUT INSERTED.id, INSERTED.value
        INTO #data
    values (1337);

select * from #data;

insert into relation (someid, dataid) 
select 
    42, id 
from #data;

select * from relation;
Related Question