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.

 
1. <TestList>
2. <Test>
3. <Insert>
4. <ID>1</ID>
5. <Name>Name 1</Name>
6. <Address>Address 1</Address>
7. <Place>Place 1</Place>
8. </Insert>
9. </Test>
10. <Test>
11. <Insert>
12. <ID>2</ID>
13. <Name>Name 2</Name>
14. <Address>Address 2</Address>
15. <Place>Place 2</Place>
16. </Insert>
17. </Test>
18. </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

 
1. INSERT INTO Test(ID,Name,Address,Place)
2. SELECT    ID,Name,[Address],Place
3. FROM OPENXML( @hdoc, 'TestList/Test/Insert')
4. WITH (
5. ID            int            'ID',
6. Name        varchar(100)    'Name',
7. Address        varchar(100)    'Address',
8. Place        varchar(50)        'Place'
9. )

 

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)

 
1. '<TestList>
2. <Test>
3. <Insert>
4. <ID>1</ID>
5. <Name>Name 1</Name>
6. <Address>Address 1</Address>
7. <Place>Place 1</Place>
8. </Insert>
9. </Test>
10. <Test>
11. <Insert>
12. <ID>2</ID>
13. <Name>Name 2</Name>
14. <Address>Address 2</Address>
15. <Place>Place 2</Place>
16. </Insert>
17. </Test>
18. <Test>
19. <Update>
20. <ID>1</ID>
21. <Name>Name 3</Name>
22. <Address>Address 3</Address>
23. <Place>Place 3</Place>
24. </Update>
25. </Test>
26. <Test>
27. <Update>
28. <ID>2</ID>
29. <Name>Name 4</Name>
30. <Address>Address 4</Address>
31. <Place>Place 4</Place>
32. </Update>
33. </Test>
34. <Test>
35. <Delete>
36. <ID>1</ID>
37. </Delete>
38. </Test>
39. </TestList>'

 

The procedure (Which Does the Magic)

 
1. Create PROCEDURE [dbo].[InsertTest]
2. (
3. @doc     XML
4. )
5. AS
6. BEGIN
7. Declare @hdoc int
8. EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
9. INSERT INTO Test
10. (
11. ID,
12. Name,
13. Address,
14. Place
15. )
16. SELECT        ID,
17. Name,
18. [Address],
19. Place
20. FROM OPENXML( @hdoc, 'TestList/Test/Insert')
21. WITH (
22. ID            int                'ID',
23. Name            varchar(100)    'Name',
24. Address        varchar(100)    'Address',
25. Place        varchar(50)        'Place'
26. )
27. UPDATE Test SET
28. Name = A.Name
29. Address = A.Address,
30. Place = A.Place
31. FROM OPENXML( @hdoc, 'TestList/Test/Update')
32. WITH (
33. ID            int                'ID',
34. Name            varchar(100)    'Name',
35. Address        varchar(100)    'Address',
36. Place        varchar(50)        'Place'
37. )A
38. INNER JOIN Test B
39. on B.ID = A.ID
40. DELETE FROM Test
41. FROM OPENXML( @hdoc, 'TestList/Test/Delete')
42. WITH (
43. ID        int        'ID'
44. )A
45. WHERE A.ID = Test.ID
46. END

 

Hope you techies out there like this idea of handling multiple DML statements and data management in one stored procedure.

Let me know your thoughts!! J