Multiple inserts of batch insert over the wire?

Question

I am generating about 500 records.

What I want to do is INSERT them all at the same time because:

• The server is located elsewhere and I would rather send one big payload over.
• This seems like less overhead than sending 500 separate insert queries over the wire.

But because they're batch inserting, I don't want to rollback everything if one insert fails - I want to try to insert the rest in there and record the failure(s).

What's the best way to do this in SQL Server? Is BULK INSERT what I want?

Answer 1

I'm not saying this is the best way, but here is one way.

My solution uses xml which, I think, has a max size of 2gb. Table Valued Parameters would have been ideal, but they are not supported in remote calls.

Since you are only generating around 500 records, you probably won't run into any size limitations.

---

On the source instance, create/load a table to play with. This will be the source data to send to the remote process.

DROP TABLE IF EXISTS dbo.customer
CREATE TABLE [dbo].[Customer] (
    [CustomerID] [int] NULL
    ,[Name] [varchar](30) NULL
    ,[RecordCreated] [datetime] NULL
    ,[RecordUpdated] [datetime] NULL
    ,
    ) ON [PRIMARY]
GO

INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (1, N'James', CAST(N'2017-11-01T16:16:21.297' AS DateTime), CAST(N'2017-11-01T16:52:02.427' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (2, N'John', CAST(N'2017-11-01T16:41:52.347' AS DateTime), CAST(N'2017-11-01T16:41:52.347' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (3, N'Sam', CAST(N'2017-11-01T16:50:25.430' AS DateTime), CAST(N'2017-11-01T16:50:25.430' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (1, N'James', CAST(N'2017-11-01T16:16:21.297' AS DateTime), CAST(N'2017-11-01T16:52:02.427' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (2, N'John', CAST(N'2017-11-01T16:41:52.347' AS DateTime), CAST(N'2017-11-01T16:41:52.347' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (3, N'Sam', CAST(N'2017-11-01T16:50:25.430' AS DateTime), CAST(N'2017-11-01T16:50:25.430' AS DateTime))
GO

---

Here is some TSQL to run on the source instance, but before you can fully execute it, you'll have to define the receiving stored procedure on the remote server. I'll get to that.

--Declare XML variable and populate by selecting from a table using FOR XML AUTO
DECLARE @x XML = (
        SELECT *
        FROM Customer
        FOR XML AUTO
            ,TYPE
            ,ROOT('Root')
        );

--You cant ship XML via remote call, so declare a Varchar(max) variable and convert the XML
DECLARE @XVarchar VARCHAR(max)
SET @XVarchar = convert(VARCHAR(max), @x)

--Call the remote stored procedure and pass the xml
--My linked server is called sqlcompare

--NOTE: you cannot execute this until you have defined the 
--receiving stored procedure on the remote server.
EXEC sqlcompare.scutility.dbo.[ProcessXmlRows] @xVarchar

---

Now, on the remote server, create a stored procedure to receive the passed xml. In the following example, I'm using OPENXML to basically shred the xml and inserting directly into the remote table. You'd probably need a cursor to help you weed out the good and bad rows. Note, that I have also created the customer table on the remote server (this allows me to reference that table in the OPENXML using the WITH clause.

CREATE PROCEDURE [dbo].[ProcessXmlRows] (@Xml xml)
AS
BEGIN
DECLARE @intXMLDocID INT -- xml document handle

--Prepare the XML that was passed into the SP
EXECUTE [master].dbo.sp_xml_preparedocument @hdoc = @intXMLDocID OUTPUT
    ,@xmltext = @xml;

--Using OPENXML, and referencing the existing customer table, insert the rows
insert into Customer
SELECT *
FROM OPENXML(@intXMLDocID, '/Root/Customer') WITH customer

select * from Customer

END
GO

Note, that I'm using with customer on the OPENXML. From the documentation on OPENXML

The WITH clause provides a rowset format (and additional mapping information as required) by using either SchemaDeclaration or specifying an existing TableName.

---

At this point, you should be able to successfully execute the source T-SQL to call the remote stored procedure and process the data.