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