If it's possible to add a little code to the current process, you might be able to cheat a little.
--Demo setup
DROP TABLE IF EXISTS [dbo].[CUSTOMER]
CREATE TABLE [dbo].[CUSTOMER](
[CustomerID] [int] NOT NULL,
[FirstName] [varchar](20) NOT NULL,
[LastName] [varchar](20) NOT NULL,
[Email] [varchar](30) NOT NULL,
[PhoneNo] [int] NOT NULL,
[StreetAddress] [varchar](40) NULL,
[City] [varchar](20) NULL,
[OrderID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Test]
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (13579246, N'David', N'Casey', N'[email protected]', 12826363, N'34 Bray Rd', N'Dublin', 94724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (16579946, N'Donal', N'Smith', N'[email protected]', 13826563, N'78 Ballybrack Rd', N'Dublin', 14724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (17579946, N'Steve', N'Reidy', N'[email protected]', 13827563, N'22 Conna Rd', N'Cork', 64724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (18579946, N'Michael', N'Yule', N'[email protected]', 14827563, N'44 Blasket Rd', N'Kilkenny', 44424274)
GO
Outside of any transaction, create a new table to hold some XML
drop table if exists XmlDataTable
CREATE TABLE XmlDataTable (XmlDataColumn xml)
Now, in your transaction, create your global temp table and select from it converting to xml raw. Then insert that value into XmlDataTable
.
begin transaction
drop table if exists ##temp
SELECT * INTO ##temp FROM customer --This is your normal create for the global temp table
DECLARE @XmlData XML
--select the data from the global temp table as xml raw
SET @XmlData = (SELECT * FROM ##temp FOR XML raw)
--insert that value into LargeVarcharTable
INSERT INTO XmlDataTable VALUES (@XmlData)
--rollback
In another session, you should be able to select * from xmldatatable (nolock)
to select the raw xml.
select * from xmldatatable (nolock)
<row CustomerID="13579246" FirstName="David" LastName="Casey" Email="[email protected]" PhoneNo="12826363" StreetAddress="34 Bray Rd" City="Dublin" OrderID="94724274"/>
<row CustomerID="16579946" FirstName="Donal" LastName="Smith" Email="[email protected]" PhoneNo="13826563" StreetAddress="78 Ballybrack Rd" City="Dublin" OrderID="14724274"/>
<row CustomerID="17579946" FirstName="Steve" LastName="Reidy" Email="[email protected]" PhoneNo="13827563" StreetAddress="22 Conna Rd" City="Cork" OrderID="64724274"/>
<row CustomerID="18579946" FirstName="Michael" LastName="Yule" Email="[email protected]" PhoneNo="14827563" StreetAddress="44 Blasket Rd" City="Kilkenny" OrderID="44424274"/>