Multiple Record Insertions

For some situations, we need to insert multiple records into SQL tables. In normal case, we do record insertions one by one. Most of our application designs have separate procedures for update and delete on the DB side, and the application invokes these procedures every time it needs to insert a record. This will affect the performance of the system as there would many DB calls originating from the application. From an enterprise perspective, DB calls are very expensive. Any system we design which has DB interactions should have minimal amount of DB calls.

Here, we explore the ways on how to handle the multiple DML operations and with multiple SQL statements. It is at this point that we need to remember our greatest B2B data management technique known as “XML”!

Analysis

Let’s try to insert more than one record into a table.

Suppose the table name is ‘Test’, and the fields are: – ID, Name, Address, Place. Then a possible way of xml would be as shown below.

<TestList> <Test> <Insert> <ID>1</ID> <Name>Name 1</Name> <Address>Address 1</Address> <Place>Place 1</Place> </Insert> </Test> <Test> <Insert> <ID>2</ID> <Name>Name 2</Name> <Address>Address 2</Address> <Place>Place 2</Place> </Insert> </Test> </TestList>

Here ‘TestList’ is representing the List of records and ‘Test’ represents Table name. The ‘Insert’ tag is identifying these records for Insert purpose. Now, let’s pass this whole XML as the parameter into the procedure.

The core logic of the procedure is

INSERT INTO Test(ID,Name,Address,Place) SELECT ID,Name,[Address],Place FROM OPENXML( @hdoc, 'TestList/Test/Insert') WITH ( ID int 'ID', Name varchar(100) 'Name', Address varchar(100) 'Address', Place varchar(50) 'Place' )

Here in OPENXML keyword is used for opening the XML string. Here the data for insertion is inside the following path ‘TestList/Test/Insert’. The actual data elements under this path are ID, Name, Address and Place. The next step in the SQL query is to assign the table fields to XML tag names. i.e.,

ID int ‘ID’

After associating each of these elements with table fields, we are done with the logic of insert stored procedure. Now we can execute the procedure with xml string. It will insert multiple records in a single Insert statement.

This method illustrates how we could insert multiple data with just one DB call for insertion. Pretty neat!

We can use the same logic explained above also for SQL table data updations and data deletions. But in “update” we need to include the Condition. For this, we can use the Aliasing names.

And the main advantage of this procedure is that we can include more than one DML operation.
The example is :-

XML String: (Acts as Input Parameter for the Stored Procedure)

'<TestList> <Test> <Insert> <ID>1</ID> <Name>Name 1</Name> <Address>Address 1</Address> <Place>Place 1</Place> </Insert> </Test> <Test> <Insert> <ID>2</ID> <Name>Name 2</Name> <Address>Address 2</Address> <Place>Place 2</Place> </Insert> </Test> <Test> <Update> <ID>1</ID> <Name>Name 3</Name> <Address>Address 3</Address> <Place>Place 3</Place> </Update> </Test> <Test> <Update> <ID>2</ID> <Name>Name 4</Name> <Address>Address 4</Address> <Place>Place 4</Place> </Update> </Test> <Test> <Delete> <ID>1</ID> </Delete> </Test> </TestList>'

The procedure (Which Does the Magic)

Create PROCEDURE [dbo].[InsertTest] ( @doc XML ) AS BEGIN Declare @hdoc int EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc INSERT INTO Test ( ID, Name, Address, Place ) SELECT ID, Name, [Address], Place FROM OPENXML( @hdoc, 'TestList/Test/Insert') WITH ( ID int 'ID', Name varchar(100) 'Name', Address varchar(100) 'Address', Place varchar(50) 'Place' ) UPDATE Test SET Name = A.Name Address = A.Address, Place = A.Place FROM OPENXML( @hdoc, 'TestList/Test/Update') WITH ( ID int 'ID', Name varchar(100) 'Name', Address varchar(100) 'Address', Place varchar(50) 'Place' )A INNER JOIN Test B on B.ID = A.ID DELETE FROM Test FROM OPENXML( @hdoc, 'TestList/Test/Delete') WITH ( ID int 'ID' )A WHERE A.ID = Test.ID END