SQL Server: Is it possible to insert into two tables at the same time?

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE InsetIntoTwoTable

(
@name nvarchar(50),
@Email nvarchar(50)
)

AS
BEGIN

SET NOCOUNT ON;


insert into dbo.info(name) values (@name)
insert into dbo.login(Email) values (@Email)
END
GO

I want to stress on using

SET XACT_ABORT ON;

for the MSSQL transaction with multiple sql statements.

See:

[To see links please register here]

They provide a very good example.

So, the final code should look like the following:

SET XACT_ABORT ON;

BEGIN TRANSACTION
DECLARE @DataID int;
INSERT INTO DataTable (Column1 ...) VALUES (....);
SELECT @DataID = scope_identity();
INSERT INTO LinkTable VALUES (@ObjectID, @DataID);
COMMIT