3

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?

3
  • one insert fails what is the expected error(for what reason do you think it will fail) ? depending of it , you can have different approach ; My first idea , is to have a staging table (no constraints, no PK,no FKs,..) , where you can dump your records.
    – Sabin B
    Feb 6, 2018 at 6:26
  • 3
    what tool are you using on the client side ? command line or application with ADO.NET for instance ? Feb 6, 2018 at 13:11
  • i think one important question is not answer.how you are connecting to remote server ?db to db or any web service ?
    – KumarHarsh
    Feb 12, 2018 at 3:09

1 Answer 1

3
+100

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.

1
  • IMHO,i do not agree with answer provided.you have just address minor problem.How to send 500 records ?how to record " BACK " failed record ?More over "You'd probably need a cursor to help you weed out the good and bad rows" why you suggest cursor when you are not sure of biz. rule to exclude records.
    – KumarHarsh
    Feb 12, 2018 at 3:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.