Handling data operation, like Insert/Update/Delete in Bulk, is always a crucial thing for a developer, when dealing with performance and less-codes.
For example, inserting 100 records together into same table, was always a pain, when he iterates the T-SQL INSERT code in a for-loop, something like:
1: for(int i=0; i<100; i++)
2: {
3: //
4: // Perform T-SQL INSERT for Record [i]
5: //
6: }
In the similar manner, the T-SQL UPDATE/DELETE code looks like:
1: for(int i=0; i<100; i++)
2: {
3: //
4: // Perform T-SQL UPDATE/DELETE for Record [i]
5: //
6: }
There is nothing wrong with the code, since it provides the intended behavior. But, it inserts 100 records, one-by-one. When coming to performance, each time the code has to connect to your database to perform the insertion. In short, if you are inserting 100 records using the above method, then your code has to connect to the Database 100 times. Isn’t that weird?
Fortunately, Sql Server has a solution for this. Sql Server comes with a data-type: Xml, which makes this possible to Insert/Update/Delete bulk records at once and is handy. The records to INSERT/UPDATE/DELETE has to be XML formatted and is passed on to a Stored Procedure, via a stored-procedure parameter of data-type: Xml.
And, here is a sample for that.
Table Structure
Create a table: Student with following structure:
| Column |
Data-Type |
| Id |
Int (auto-increment)/(Primary-Key) |
| Name |
Varchar(50) |
| Department |
Varchar(50) |
| Division |
Varchar(50) |
Insert Bulk Data
Now, for inserting the bulk data, I’m creating a stored-procedure with parameter (@StudentData) of data-type: Xml which accepts the bulk data.
1: Create Procedure prI_BulkStudentData(@StudentData Xml)
2: As
3:
4: -- Add Handle to XML Data
5: Declare @xmlDataHandle Int
6: Exec sp_xml_preparedocument @xmlDataHandle Output, @StudentData
7:
8: Insert Into Student(Name, Department, Division)
9: Select xmlName, xmlDepartment, xmlDivision From OpenXml(@xmlDataHandle, '/BulkData/Student', 2)
10: With(
11: xmlName VarChar(50),
12: xmlDepartment VarChar(50),
13: xmlDivision VarChar(50)
14: )
15:
16: -- Remove Handle to Free-Up Memory
17: Exec sp_xml_removedocument @xmlDataHandle
18:
19: Go
Suppose, I’ve to insert 10 student records. Then, I need to build a XML structure similar to
1: <BulkData>
2: <Student>
3: <xmlName>Name 1</xmlName>
4: <xmlDepartment>Department 1</xmlDepartment>
5: <xmlDivision>Division 1</xmlDivision>
6: </Student>
7: <Student>
8: <xmlName>Name 2</xmlName>
9: <xmlDepartment>Department 2</xmlDepartment>
10: <xmlDivision>Division 2</xmlDivision>
11: </Student>
12: <Student>
13: <xmlName>Name 3</xmlName>
14: <xmlDepartment>Department 3</xmlDepartment>
15: <xmlDivision>Division 3</xmlDivision>
16: </Student>
17: <Student>
18: <xmlName>Name 4</xmlName>
19: <xmlDepartment>Department 4</xmlDepartment>
20: <xmlDivision>Division 4</xmlDivision>
21: </Student>
22: <Student>
23: <xmlName>Name 5</xmlName>
24: <xmlDepartment>Department 5</xmlDepartment>
25: <xmlDivision>Division 5</xmlDivision>
26: </Student>
27: <Student>
28: <xmlName>Name 6</xmlName>
29: <xmlDepartment>Department 6</xmlDepartment>
30: <xmlDivision>Division 6</xmlDivision>
31: </Student>
32: <Student>
33: <xmlName>Name 7</xmlName>
34: <xmlDepartment>Department 7</xmlDepartment>
35: <xmlDivision>Division 7</xmlDivision>
36: </Student>
37: <Student>
38: <xmlName>Name 8</xmlName>
39: <xmlDepartment>Department 8</xmlDepartment>
40: <xmlDivision>Division 8</xmlDivision>
41: </Student>
42: <Student>
43: <xmlName>Name 9</xmlName>
44: <xmlDepartment>Department 9</xmlDepartment>
45: <xmlDivision>Division 9</xmlDivision>
46: </Student>
47: <Student>
48: <xmlName>Name 10</xmlName>
49: <xmlDepartment>Department 10</xmlDepartment>
50: <xmlDivision>Division 10</xmlDivision>
51: </Student>
52: </BulkData>
To perform a bulk-insert, all I need is to execute the stored-procedure, along with this Xml bulk-data, as follows:
1: Exec prI_BulkStudentData '<BulkData>
2: <Student>
3: <xmlName>Name 1</xmlName>
4: <xmlDepartment>Department 1</xmlDepartment>
5: <xmlDivision>Division 1</xmlDivision>
6: </Student>
7: <Student>
8: <xmlName>Name 2</xmlName>
9: <xmlDepartment>Department 2</xmlDepartment>
10: <xmlDivision>Division 2</xmlDivision>
11: </Student>
12: <Student>
13: <xmlName>Name 3</xmlName>
14: <xmlDepartment>Department 3</xmlDepartment>
15: <xmlDivision>Division 3</xmlDivision>
16: </Student>
17: <Student>
18: <xmlName>Name 4</xmlName>
19: <xmlDepartment>Department 4</xmlDepartment>
20: <xmlDivision>Division 4</xmlDivision>
21: </Student>
22: <Student>
23: <xmlName>Name 5</xmlName>
24: <xmlDepartment>Department 5</xmlDepartment>
25: <xmlDivision>Division 5</xmlDivision>
26: </Student>
27: <Student>
28: <xmlName>Name 6</xmlName>
29: <xmlDepartment>Department 6</xmlDepartment>
30: <xmlDivision>Division 6</xmlDivision>
31: </Student>
32: <Student>
33: <xmlName>Name 7</xmlName>
34: <xmlDepartment>Department 7</xmlDepartment>
35: <xmlDivision>Division 7</xmlDivision>
36: </Student>
37: <Student>
38: <xmlName>Name 8</xmlName>
39: <xmlDepartment>Department 8</xmlDepartment>
40: <xmlDivision>Division 8</xmlDivision>
41: </Student>
42: <Student>
43: <xmlName>Name 9</xmlName>
44: <xmlDepartment>Department 9</xmlDepartment>
45: <xmlDivision>Division 9</xmlDivision>
46: </Student>
47: <Student>
48: <xmlName>Name 10</xmlName>
49: <xmlDepartment>Department 10</xmlDepartment>
50: <xmlDivision>Division 10</xmlDivision>
51: </Student>
52: </BulkData>'
This’ll insert 10 student records into the Table: Student.
Bulk Update Data
Now, suppose I want to Update the Student records in bulk, say, I want to capitalize the name (ie, Name 6 –> NAME 6, etc.) of each student with Id ranging from 6-10. Then, the store- procedure for bulk updating looks like
1: Create Procedure prU_BulkStudentData(@StudentData Xml)
2: As
3:
4: -- Add Handle to XML Data
5: Declare @xmlDataHandle Int
6: Exec sp_xml_preparedocument @xmlDataHandle Output, @StudentData
7:
8: Update Student Set Student.Name=_xmlStudent.xmlName
9: From OpenXml(@xmlDataHandle, '/BulkData/Student', 2)
10: With(
11: xmlId Int,
12: xmlName VarChar(50)
13: )_xmlStudent
14: Where Student.Id=_xmlStudent.xmlId
15:
16: -- Remove Handle to Free-Up Memory
17: Exec sp_xml_removedocument @xmlDataHandle
18:
19: Go
And I can bulk update the Student records by executing the stored-procedure as
1: Exec prU_BulkStudentData '<BulkData>
2: <Student>
3: <xmlId>6</xmlId>
4: <xmlName>NAME 6</xmlName>
5: </Student>
6: <Student>
7: <xmlId>7</xmlId>
8: <xmlName>NAME 7</xmlName>
9: </Student>
10: <Student>
11: <xmlId>8</xmlId>
12: <xmlName>NAME 8</xmlName>
13: </Student>
14: <Student>
15: <xmlId>9</xmlId>
16: <xmlName>NAME 9</xmlName>
17: </Student>
18: <Student>
19: <xmlId>10</xmlId>
20: <xmlName>NAME 10</xmlName>
21: </Student>
22: </BulkData>'
Bulk Delete Data
Suppose you want to delete the Student records of Id ranging from 11-15. Then the stored-procedure for deleting bulk data looks like
1: Create Procedure prD_BulkStudentData(@StudentData Xml)
2: As
3:
4: -- Add Handle to XML Data
5: Declare @xmlDataHandle Int
6: Exec sp_xml_preparedocument @xmlDataHandle Output, @StudentData
7:
8: Delete From Student
9: From OpenXml(@xmlDataHandle, '/BulkData/Student', 2)
10: With(
11: xmlId Int
12: )_xmlStudent
13: Where Student.Id=_xmlStudent.xmlId
14:
15: -- Remove Handle to Free-Up Memory
16: Exec sp_xml_removedocument @xmlDataHandle
17:
For deleting the student records from 11-15, you can execute the stored-procedure as
1: Exec prD_BulkStudentData '<BulkData>
2: <Student>
3: <xmlId>11</xmlId>
4: </Student>
5: <Student>
6: <xmlId>12</xmlId>
7: </Student>
8: <Student>
9: <xmlId>13</xmlId>
10: </Student>
11: <Student>
12: <xmlId>14</xmlId>
13: </Student>
14: <Student>
15: <xmlId>15</xmlId>
16: </Student>
17: </BulkData>'
Select/Preview Xml Contents
Sometimes, as part of trouble shooting you may want to see whether the Xml structure that you had passed on to the store-procedure. In that case you may create a stored-procedure like
1: Create Procedure prS_BulkStudentData(@StudentData Xml)
2: As
3:
4: -- Add Handle to XML Data
5: Declare @xmlDataHandle Int
6: Exec sp_xml_preparedocument @xmlDataHandle Output, @StudentData
7:
8: Select xmlName, xmlDepartment, xmlDivision From OpenXml(@xmlDataHandle, '/BulkData/Student', 2)
9: With(
10: xmlName VarChar(50),
11: xmlDepartment VarChar(50),
12: xmlDivision VarChar(50)
13: )
14:
15: -- Remove Handle to Free-Up Memory
16: Exec sp_xml_removedocument @xmlDataHandle
17:
18: Go
The stored-procedure returns the XML data that you’ve passed on.
For example, if you pass an Xml structure like
1: Exec prS_BulkStudentData '<BulkData>
2: <Student>
3: <xmlName>Name 1</xmlName>
4: <xmlDepartment>Department 1</xmlDepartment>
5: <xmlDivision>Division 1</xmlDivision>
6: </Student>
7: <Student>
8: <xmlName>Name 2</xmlName>
9: <xmlDepartment>Department 2</xmlDepartment>
10: <xmlDivision>Division 2</xmlDivision>
11: </Student>
12: <Student>
13: <xmlName>Name 3</xmlName>
14: <xmlDepartment>Department 3</xmlDepartment>
15: <xmlDivision>Division 3</xmlDivision>
16: </Student>
17: <Student>
18: <xmlName>Name 4</xmlName>
19: <xmlDepartment>Department 4</xmlDepartment>
20: <xmlDivision>Division 4</xmlDivision>
21: </Student>
22: <Student>
23: <xmlName>Name 5</xmlName>
24: <xmlDepartment>Department 5</xmlDepartment>
25: <xmlDivision>Division 5</xmlDivision>
26: </Student>
27: <Student>
28: <xmlName>Name 6</xmlName>
29: <xmlDepartment>Department 6</xmlDepartment>
30: <xmlDivision>Division 6</xmlDivision>
31: </Student>
32: <Student>
33: <xmlName>Name 7</xmlName>
34: <xmlDepartment>Department 7</xmlDepartment>
35: <xmlDivision>Division 7</xmlDivision>
36: </Student>
37: <Student>
38: <xmlName>Name 8</xmlName>
39: <xmlDepartment>Department 8</xmlDepartment>
40: <xmlDivision>Division 8</xmlDivision>
41: </Student>
42: <Student>
43: <xmlName>Name 9</xmlName>
44: <xmlDepartment>Department 9</xmlDepartment>
45: <xmlDivision>Division 9</xmlDivision>
46: </Student>
47: <Student>
48: <xmlName>Name 10</xmlName>
49: <xmlDepartment>Department 10</xmlDepartment>
50: <xmlDivision>Division 10</xmlDivision>
51: </Student>
52: </BulkData>'
Then you’ll get an output similar to
Point of Interest
-
The Xml tags are Case Censitive.
-
Supported from Sql Server 2005 and higher.
-
<BulkData> – represents the root node of the XML.
-
<Student> – represents each student record. Please note, it is not necessary that the name should be same as that of the table name into which you are inserting the record. It can be any name, say <xmlStudent> instead of <Student>.
-
<xmlName>, <xmlDepartment>, <xmlDivision> – represents the column into which you want to insert data.
-
'/BulkData/Student' – represents the XPath pattern used to identify the nodes (in the XML document whose handle is passed in the @xmlDataHandle parameter) to be processed as rows.
-
sp_xml_preparedocument – returns a handle that can be used to access the newly created internal representation of the XML document. This handle is valid for the duration of the session or until the handle is invalidated by executing sp_xml_removedocument.
-
sp_xml_removedocument - Removes the internal representation of the XML document specified by the document handle and invalidates the document handle.
-
OpenXml - provides a rowset view over an XML document.
Hope this is Handy.
You can download the T-SQL sample script from the here.