Wednesday, March 25, 2009

Bulk insert of data

Recently I faced the problem of inserting a big amount of data into the database. The point is that multiple tables are affected which are connected with foreign keys. After some research I found OpenXML as one possibility. You create a stored procedure where you read your XML string into an "XmlDocument" which you can then query by using XPath and inserting, updating or deleting the tables occordingly.
The other possibility I found is SQLXML which is now available in version 4.1. It is delivered with SQL Server but not with the Express version. If you need it you can download it from here. SQLXML allows you to bulk insert data from an XML file by using a so called annotated XSD schema where the mapping is defined. It is not very difficult to define the schema, only recursion and special cases are a little bit difficult to handle. I was not able to make recursion work with identity values coming from the database. Anyway; I did some performance tests where I had a structure like:
Table A
Table B has a foreign key to Table A
Table C has a foreign key to Table B
which corresponds to the following XML structure:
<root>
<a>
<b>
<c></c>
</b>
</a>
</root>

I created an XML file that contained 100 A's; every A contained 100 B's and every B contained 100 C's. This gives a total of 100 A's, 10.000 B's and 1.000.000 C's. To insert all them and updating the foreign keys it took 84 seconds. This is quite fast for such an amount of data. I think that this is the fastest way to bulk insert data into SQL Server (if I'm wrong You are welcome to coment on this).
If you want to use it with .Net add a reference to "Microsoft SQL Server Bulk Upload" or something like that.
I don't remember the exact name but as soon as I find the link of the code sample I will post it here.

The problem is that SQLXML cannot be used for updates. There exist so called updategrams or diffgrams but I didn't try them.

2 comments:

Juri Strumpflohner said...

The XML isn't displayed, most probably due to the less and greater symbols. You have to replace them with the appropriate unicodes. Time ago I've written a very basic program that does this job for you: Web Code Formatter

Manfred said...

Thanks, for the correction :-)