Insert data in relation table and into data table with less query cost

Question

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.

Answer 1

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;

Answer 2

I thought of something like this, looking at your problem. It uses the EXCEPT operator and a common table expression. This would allow you to fill up your data table and then eventually insert those values into the relation table asynchronously. An example would be a job that runs once every N hours and updates the relation table.

I changed your create table statements, because

1. I'm not sure I understand what someid is used for

2. The id field, since its using identity is sufficient for the foreign key reference between the relation and data tables

USE somedatabase;

CREATE TABLE data ( id int IDENTITY(1,1) PRIMARY KEY, value INT NOT NULL);


CREATE TABLE relation (id int IDENTITY(1,1) PRIMARY KEY, 
dataid int FOREIGN KEY REFERENCES data(id));


/* insert some rows into data table */

INSERT INTO DATA(value) values(1337)
INSERT INTO DATA(value) values(1338)
INSERT INTO DATA(value) values(1339)
GO

/* if there are new rows in data, insert them into relation */

;WITH CTE AS
(
    SELECT id
    FROM data
)
INSERT INTO relation (dataid)
SELECT id FROM CTE
EXCEPT
SELECT dataid from relation