How can I Insert/Update into two related tables in one command?

Question

A database exists with two tables

• Data_t : DataID Primary Key that is Identity 1,1. Also has another field 'LEFT' TINYINT
• Data_Link_t : DataID PK and FK where DataID MUST exist in Data_t. Also has another field 'RIGHT' SMALLINT

Coming from a microsoft access environment into C# and sql server I'm looking for a good method of importing a record into this relationship.

The record contains information that belongs on both sides of this join (Possibly inserting/updating upwards 5000 records at once). Bonus to process the entire batch in some kind of LINQ list type command but even if this is done record by record the key goal is that BOTH sides of this record should be processed in the same step.

There are countless approaches and I'm looking at too many to determine which way I should go so I thought faster to ask the general public. Is LINQ an option for inserting/updating a big list like this with LINQ to SQL? Should I go record by record? What approach should I use to add a record to normalized tables that when joined create the full record?

Answer 1

Sounds like a case where I'd write a small stored proc and call that from C# - e.g. as a function on my Linq-to-SQL data context object.

Something like:

CREATE PROCEDURE dbo.InsertData(@Left TINYINT, @Right SMALLINT)
AS BEGIN
   DECLARE @DataID INT

   INSERT INTO dbo.Data_t(Left) VALUES(@Left)

   SELECT @DataID = SCOPE_IDENTITY();

   INSERT INTO dbo.Data_Link_T(DataID, Right) VALUES(@DataID, @Right)
END

If you import that into your data context, you could call this something like:

using(YourDataContext ctx = new YourDataContext)
{
   foreach(YourObjectType obj in YourListOfObjects)
   {
      ctx.InsertData(obj.Left, obj.Right)
   }
}

and let the stored proc handle all the rest (all the details, like determining and using the IDENTITY from the first table in the second one) for you.

Answer 2

I have never tried it myself, but you might be able to do exactly what you are asking for by creating an updateable view and then inserting records into the view.

UPDATE

I just tried it, and it doesn't look like it will work.

Msg 4405, Level 16, State 1, Line 1 View or function 'Data_t_and_Data_Link_t' is not updatable because the modification affects multiple base tables.

I guess this is just one more thing for all the Relational Database Theory purists to hate about SQL Server.

ANOTHER UPDATE

Further research has found a way to do it. It can be done with a view and an "instead of" trigger.

create table Data_t 
(
    DataID int not null identity primary key,
    [LEFT] tinyint,
)
GO

create table Data_Link_t 
(
    DataID int not null primary key foreign key references Data_T (DataID),
    [RIGHT] smallint,
)
GO

create view Data_t_and_Data_Link_t 
as
select
    d.DataID,
    d.[LEFT],
    dl.[RIGHT]
from
    Data_t d
    inner join Data_Link_t dl on dl.DataID = d.DataID 
GO

create trigger trgInsData_t_and_Data_Link_t on Data_t_and_Data_Link_T
instead of insert
as
insert into Data_t ([LEFT]) select [LEFT] from inserted 
insert into Data_Link_t (DataID, [RIGHT]) select @@IDENTITY, [RIGHT] from inserted
go

insert into Data_t_and_Data_Link_t ([LEFT],[RIGHT]) values (1, 2)