CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
Chaitanya
Chaitanya

Reputation: 5293

Composing SQL insert statement into another insert statement

I know that in T-SQL (Server 2008 R2) I can use the 'Output' keyword to get the Id of a row I just inserted. For example, I can do

insert into [Membership].[dbo].[User] (EmailAddress) 
output Inserted.UserId 
values('[email protected]')

Is there any way of composing this into another insert? For example, lets say I want to add a new user and immediately add that user to a UserRole table which maps the UserId to a RoleId.

Basically, I would like to do something like below.

insert into UserRole (RoleId, UserId) 
values 
(
    1, 
    insert into [Membership].[dbo].[User] (EmailAddress) 
    output Inserted.UserId values('[email protected]')
)

But I can't seem to get this to work. I tried wrapping the internal insert in brackets () or using a select * from () etc.

What am I missing? Is this composition even possible?

Thanks for the help.

Regards,

Upvotes: 0

Views: 118

Answers (2)

gotqn
gotqn

Reputation: 43626

Another solution is to use triggers:

http://msdn.microsoft.com/en-us/library/ms189799.aspx

and pay attention to "after" insert triggers:

FOR | AFTER AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.

AFTER is the default when FOR is the only keyword specified.

AFTER triggers cannot be defined on views.

Upvotes: 0

marc_s
marc_s

Reputation: 754240

You would have to capture the output into a table variable:

DECLARE @TempVar TABLE (UserID INT)

insert into [Membership].[dbo].[User] (EmailAddress) 
output Inserted.UserId INTO @TempVar(UserID)
values('[email protected]')

and then in a second step do an insert from that temp table into the target table:

INSERT INTO dbo.UserRole (RoleId, UserId) 
   SELECT 
      (yourRoleId), tv.UserID
   FROM @TempVar tv

You could also direct the OUTPUT clause directly into the target table - that'll work if you can e.g. use a fixed value for your RoleID:

DECLARE @FixedRoleID INT = 42

INSERT INTO [Membership].[dbo].[User] (EmailAddress) 
OUTPUT @FixedRoleID, Inserted.UserId INTO dbo.UserRole(RoleId, UserId)
VALUES ('[email protected]')

Upvotes: 2

Related Questions