But why would you want to do this?

Good question! Why not write 2 insert statements to do this? Yeah, that's a possibility too. But this one has some advantages.

Let's say you have a Main Table, for example Projects. You also have a Linked Table ProjectMembers. When you insert a Project, the Project Manager should be added to the ProjectMenbers as well.

For that you might need the ProjectID, which is set upon insert of the Project, for the insert into the ProjectMembers. That's where this one comes in.

Take a look at this example

As you can see with the Insert Statement, the Output is used to do a second insert into the second table. And there you can use the values from the inserted record. This makes sure that the LinkID from the LinkTable is the same as the ID from the MainTable.

I also used the Identity Column from the MainTable to create a description for the LinkTable. So yes, every default value from the MainTable can be used in the LinkTable insert.

A great way to avoid Triggers

Another way to do this would be to add a Trigger to the MainTable to insert the record in the LinkTable. But Triggers are notorious and not without a reason. Now I am not saying you shouldn't use trigger though. But if there is a way to avoid a Trigger, most often it is better to just that.

Triggers are great, but do have some impact on the performance. But for me that's not the main reason to use them only when they serve a real purpose. THe main reason is that you cannot see them directly and that might give some unexpected results. Sure, you can (and should) check for them in an unknown database. But hey, we have all been there. Right?

Speaking of Triggers

Do note that with this Multiple Insert Statement, defaults from the main table can be used for the insert in the link table. But values that are set by an Insert Trigger will be set only after the insert is completed. Check out this example:

create table MainTable(
  ID uniqueidentifier not null default newid() primary key nonclustered,
  HID int identity,
  Description nvarchar(50)
)

create table LinkTable(
  ID uniqueidentifier not null default newid() primary key nonclustered,
  HID int identity,
  LinkID uniqueidentifier,
  Description nvarchar(50)
)
GO

create trigger trMainTableInsert
on MainTable
after Insert
as
begin
  declare @ID uniqueidentifier
  declare @HID int
  declare @Description nvarchar(50)

  select @ID = ID from inserted
  select @Description = concat(Description, ' ', HID) from inserted

  update MainTable
  set Description = @Description
  where ID = @ID
end
GO

insert into MainTable(Description)
output inserted.ID, inserted.Description
into LinkTable(LinkID, Description)
values('Test')
GO 50

select * from MainTable
select * from LinkTable
GO

drop table MainTable
drop table LinkTable
GO

As you can see with this example, the value that is passed to the second insert is the value as inserted. Not the value that was set by the trigger. So keep in mind that the flow is:

First Insert Statement
Second Insert Statement using the output of the First Insert
The After Insert trigger(s)

That's it for today!

I hope you enjoy this one. It is supported from SQL 2014 and up so do check the version you are working against.

Let me know if and how you used this in your projects. And stay tuned for more tips & tricks on SQL.

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!