I just want to show the inserted and deleted record in the COMMIT TRANSACTION errors so we can compare the errors for duplicate keys values easier. How do I add a string to the OUTPUT clause so the transaction will execute and show inserted and updated tables as a string with the transaction errors. This is for a Transaction with 50 tables so I don't want to have to create 50 temp tables. It could be that adding a string will work. Sql Server 2016
SELECT '' 'table1', * FROM [MergeAccounts].[dbo].[table1]
begin transaction
UPDATE [dbo].[table1]
SET [IDNUM] = 11111
OUTPUT '' ins, inserted.*, '' del, deleted.*
--OUTPUT '' ins, inserted.*, '' del, deleted.* INTO @string--The target table of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. Add INTO something.
WHERE IDNUM = 11113
--commit transaction
select @@error Error
If @@error = 0
COMMIT TRANSACTION
Else
ROLLBACK TRANSACTION
;
SELECT '' 'table1', * FROM [MergeAccounts].[dbo].[table1]
Create for test database
USE [master]
GO
/****** Object: Database [MergeAccounts] Script Date: 9/17/2022 8:07:30 AM ******/
CREATE DATABASE [MergeAccounts]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'MergeAccounts', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MergeAccounts.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'MergeAccounts_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MergeAccounts_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
ALTER DATABASE [MergeAccounts] SET COMPATIBILITY_LEVEL = 150
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MergeAccounts].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [MergeAccounts] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [MergeAccounts] SET ANSI_NULLS OFF
GO
ALTER DATABASE [MergeAccounts] SET ANSI_PADDING OFF
GO
ALTER DATABASE [MergeAccounts] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [MergeAccounts] SET ARITHABORT OFF
GO
ALTER DATABASE [MergeAccounts] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [MergeAccounts] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [MergeAccounts] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MergeAccounts] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [MergeAccounts] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [MergeAccounts] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [MergeAccounts] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [MergeAccounts] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [MergeAccounts] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [MergeAccounts] SET DISABLE_BROKER
GO
ALTER DATABASE [MergeAccounts] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [MergeAccounts] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [MergeAccounts] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [MergeAccounts] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [MergeAccounts] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [MergeAccounts] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [MergeAccounts] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [MergeAccounts] SET RECOVERY FULL
GO
ALTER DATABASE [MergeAccounts] SET MULTI_USER
GO
ALTER DATABASE [MergeAccounts] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [MergeAccounts] SET DB_CHAINING OFF
GO
ALTER DATABASE [MergeAccounts] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [MergeAccounts] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [MergeAccounts] SET DELAYED_DURABILITY = DISABLED
GO
ALTER DATABASE [MergeAccounts] SET ACCELERATED_DATABASE_RECOVERY = OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'MergeAccounts', N'ON'
GO
ALTER DATABASE [MergeAccounts] SET QUERY_STORE = OFF
GO
USE [MergeAccounts]
GO
/****** Object: Table [dbo].[table1] Script Date: 9/17/2022 8:07:30 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table1](
[i1] [int] IDENTITY(1,1) NOT NULL,
[IDNUM] [int] NOT NULL,
[c1] [varchar](9) NOT NULL,
[c2] [int] NOT NULL,
[c3] [int] NOT NULL,
[c4] [varchar](23) NULL,
[c5] [numeric](8, 2) NULL,
[c6] [numeric](8, 2) NULL,
[c7] [numeric](8, 2) NOT NULL,
CONSTRAINT [PK__table1] PRIMARY KEY CLUSTERED
(
[i1] ASC,
[IDNUM] ASC,
[c1] ASC,
[c2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[table2] Script Date: 9/17/2022 8:07:30 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table2](
[i1] [int] IDENTITY(1,1) NOT NULL,
[IDNUM] [int] NOT NULL,
[c1] [varchar](9) NOT NULL,
[c2] [int] NOT NULL,
[c3] [int] NOT NULL,
[c4] [varchar](23) NULL,
[c5] [numeric](8, 2) NULL,
CONSTRAINT [PK__table2] PRIMARY KEY CLUSTERED
(
[i1] ASC,
[IDNUM] ASC,
[c1] ASC,
[c2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[table1] ON
INSERT [dbo].[table1] ([i1], [IDNUM], [c1], [c2], [c3], [c4], [c5], [c6], [c7]) VALUES (2, 11111, N'1', 2, 3, N'b', CAST(5.00 AS Numeric(8, 2)), CAST(6.00 AS Numeric(8, 2)), CAST(7.00 AS Numeric(8, 2)))
INSERT [dbo].[table1] ([i1], [IDNUM], [c1], [c2], [c3], [c4], [c5], [c6], [c7]) VALUES (4, 11111, N'1', 2, 3, N'4', CAST(5.00 AS Numeric(8, 2)), CAST(6.00 AS Numeric(8, 2)), CAST(7.00 AS Numeric(8, 2)))
INSERT [dbo].[table1] ([i1], [IDNUM], [c1], [c2], [c3], [c4], [c5], [c6], [c7]) VALUES (5, 11111, N'1', 2, 3, N'c', CAST(5.00 AS Numeric(8, 2)), CAST(6.00 AS Numeric(8, 2)), CAST(7.00 AS Numeric(8, 2)))
INSERT [dbo].[table1] ([i1], [IDNUM], [c1], [c2], [c3], [c4], [c5], [c6], [c7]) VALUES (7, 11112, N'4', 5, 6, N'd', CAST(6.00 AS Numeric(8, 2)), CAST(7.00 AS Numeric(8, 2)), CAST(8.00 AS Numeric(8, 2)))
INSERT [dbo].[table1] ([i1], [IDNUM], [c1], [c2], [c3], [c4], [c5], [c6], [c7]) VALUES (8, 11112, N'4', 5, 6, N'g', CAST(5.00 AS Numeric(8, 2)), CAST(7.00 AS Numeric(8, 2)), CAST(4.00 AS Numeric(8, 2)))
SET IDENTITY_INSERT [dbo].[table1] OFF
GO
SET IDENTITY_INSERT [dbo].[table2] ON
INSERT [dbo].[table2] ([i1], [IDNUM], [c1], [c2], [c3], [c4], [c5]) VALUES (1, 11111, N'2', 3, 4, N'a', CAST(5.00 AS Numeric(8, 2)))
INSERT [dbo].[table2] ([i1], [IDNUM], [c1], [c2], [c3], [c4], [c5]) VALUES (2, 11111, N'4', 5, 6, N'h', CAST(4.00 AS Numeric(8, 2)))
INSERT [dbo].[table2] ([i1], [IDNUM], [c1], [c2], [c3], [c4], [c5]) VALUES (3, 11112, N'4', 5, 6, N'j', CAST(4.00 AS Numeric(8, 2)))
INSERT [dbo].[table2] ([i1], [IDNUM], [c1], [c2], [c3], [c4], [c5]) VALUES (4, 11112, N'4', 5, 3, N'g', CAST(7.00 AS Numeric(8, 2)))
INSERT [dbo].[table2] ([i1], [IDNUM], [c1], [c2], [c3], [c4], [c5]) VALUES (5, 11113, N'4', 5, 3, N'g', CAST(7.00 AS Numeric(8, 2)))
SET IDENTITY_INSERT [dbo].[table2] OFF
GO
USE [master]
GO
ALTER DATABASE [MergeAccounts] SET READ_WRITE
GO