Four (4) routines

Question

Is there any possible way to execute such a subquery:

DELETE (INSERT (SELECT (INSERT)))

I know how INSERT (SELECT)::

INSERT INTO r (u_id,role) 
    VALUES ((SELECT u_id FROM USER WHERE email="[e-mail]"),'Agent');

My problem: The
user is present in the query table, when he checks his account, the system should have:

• Create user in user table
• Create a user role in the role table
• Remove user from query table

Answer 1

The reason you can do INSERT INTO .... SELECT ...this is because it is SELECTused as input to the request INSERT.

INSERT . , :

--Create user in user table
INSERT INTO UserTable VALUES (...)

--Create user role in role table
INSERT INTO UserRoles VALUES (...)

--Delete user from request table
DELETE FROM Requests WHERE ...

, :

BEGIN TRAN

--Create user in user table
INSERT INTO UserTable VALUES (...)

--Create user role in role table
INSERT INTO UserRoles VALUES (...)

--Delete user from request table
DELETE FROM Requests WHERE ...

COMMIT

, ID . , UserTable IDENTITY, SCOPE_IDENTITY:

BEGIN TRAN

--Create user in user table
INSERT INTO UserTable VALUES (...)

DECLARE @UserID INT
SET @UserID = SCOPE_IDENTITY()

--Create user role in role table
INSERT INTO UserRoles (UserID, RoleID) VALUES (@UserID, ...)

--Delete user from request table
DELETE FROM Requests WHERE ...

COMMIT