Copying ADODB Recordsets Across Databases

I currently help maintain a Classic ASP (VBscript) website. The setup is pretty standard in that we have separate development and production servers. However, while I have complete programmatic access to the production database, I don't have any sort of administrative/management console access.

This means that the dev & production data tends to drift over time and there isn't a good way to sync them up. For a long time, I've been trying to come up with a way where I can slurp all of the data out of the production database and overwrite the dev database. I certainly could've coded something up manually but there are quite a few tables and I didn't want to have to change my export code everytime the database scheme changed.

I was aware that it was trivial to convert an ADODB Recordset into XML and I was finally able to figure out how to load that XML back into a Recordset, then pipe it to a different database or table (provided the new destination has the same structure).

Dumping a Recordset to XML

This is dead simple

~~~~ {.vb name="code"} dim objXML: Set objXML = Server.CreateObject("MSXML2.DOMDocument") dim res, sql sql = "SELECT * FROM mytable" set res = ExecuteReader(sql) 'This function opens a connection and returns an ADODB.Recordset With res Call .Save(objXML, 1) Call .Close() End With Set res = nothing response.contenttype = "text/xml" response.write objXMl.xml wzxhzdk:0