0

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.

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;
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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