The XportXML utility, will help you copy relational data (a bunch of related tables) to another SQL Server. There is no rocket science behind the utility as such. It automates many mundane tasks like writing the query to export the data in hierarchical order, and creating XSD schema. This is my first contribution to SQLServerCentral.com, which I rely upon a great deal to learn and improve my skills. Of course, the utility is also posted on my personal blog, which is a collection of my rants and creativity (sometimes).
The script was originally developed in VBA using MS Access. I finally managed to convert it to VB Script(not as easy as I thought). Please make sure that your system can run VB Scripts.
Requirements
The script uses SQL DMO to generate export scripts, and SQL Bulk Loader to import data.
How to use XportXML
cscript XportXML.vbs "specification file" Example:
cscript XportXML.vbs "c:\temp\myspecs.txt" What happens next
The script creates three files.
- export_data.bat
Run this file to export data in XML format. The data file is created as exported_data.xml in the file location specified in the specification file. - exported_data.xsd
XSD schema of the exported xml file. - import_data.vbs
Run this file on the target system to import the data. The XML and XSD files are required for import.
Specification File
Specification file provides instruction to the XportXML about what you want to do. The specification file is a simple text file which can be created using Notepad.
1st Line: Connection information of the source system
2nd Line: Connection information for the target system
3rd Line: Export/Import options
4th line onward: Each line must contain one entry per table to be exported.
Note: A line may contain several parameters, separated by comma, and optionally delimited by double quotes, if necessary. A semicolon in the first column makes it a comment and is not counted toward the line count. A bracket around the parameter indicates, optional item.
1st Line: Connection information for the source database.
[<Src SQL Server User>],[<Src SQL Server Password>],<Src SQL Server>,<Src Source database>,<Src Windows Authentication><Src SQL Server User> : If you are using sql server authentication then supply the user name.
<Src SQL Server Password> : If you are using sql server authentication then supply the password.
<Src SQL Server> : Source sql server.
<Src Source database> : Source database name
<Src Windows Authentication> : Y or N.
Examples:
1. Windows authentication, extract from AdventureWorks database from localserver.,,ASUSAK\SQLEXPRESS,adventureworks,Y
2. SQL Authentication, extract from AdventureWorks database from localserver.sa,mypassword,ASUSAK\SQLEXPRESS,adventureworks,N
2nd Line: Connection information for the target database.
You can always change this information in the generated scripts later.
[<Tgt SQL Server User>],[<Tgt SQL Server Password>],<Tgt SQL Server>,<TGt Source database>,<Tgt Windows Authentication>
<Tgt SQL Server User> : If you are using sql server authentication then supply the user name.
<Tgt SQL Server Password> : If you are using sql server authentication then supply the password.
<Tgt SQL Server> : Source sql server.
<Tgt Source database> : Source database name
<Tgt Windows Authentication> : Y or N.
Examples:
1. Windows authentication, extract from AdventureWorks database from localserver.,,ASUSAK\SQLEXPRESS,adventureworks,Y
2. SQL Authentication, extract from AdventureWorks database from localserver.sa,mypassword,ASUSAK\SQLEXPRESS,adventureworks,N
3rd Line: Export options.
[<Files location>],[<Drop tgt tables>],[<Create tgt tables>],[<Keep identity column values>] <Files location> : Path where all scripts will be created. Default c:\
<Drop tgt tables>: Y or N. If Y, target tables will be dropped before import. Default N.
<Create tgt tables>: Y or N. If Y, target tables will be created if they do not exist. Default Y.
<Keep identity column values>: Y or N. If Y, if the PK is an identity column, the source values will be retained. If N, new values will be generated for the parent and child tables. Default N.
4th line onward: Exported table information
<Level>,<Src table name>,[<Tgt table name>],<[<Where clause>]<Level> : This field establishes the hierarchy of the exported XML data. The very first record must contain a value 1 for the root table. The child tables will contain 2, 3 etc.
<Src table name> : You can use [<owner>].[<table name>], [<table name>], <owner>.<tablename>, or simply <tablename> formats. If owner is not specified, dbo is assumed.
<Tgt table name> : You can use [<owner>].[<table name>], [<table name>], <owner>.<tablename>, or simply <tablename> formats. If owner is not specified, dbo is assumed. If this parameter is omitted, then a table with _xs<Src table name> will be used as the target table.
<Where clause> : Use [<table name>].[<column name>]=<Value> format. If this clause is ignored, all rows are selected.
Examples of specification file
Example a:
,,ASUSAK,Adventureworks,Y
,,ASUSAK,Lab1,Y
c:\temp
1Production.ProductCategory
2Production.ProductSubcategory
3Production.ProductIn the above example, the hierarchy is established as Category, Subcategory, and Product tables. The data will be exported into dbo._xsProductCategory, dbo._xsProductSubcategory, and dbo._xsProduct tables. The specification file above will bring less rows in the Product table than the source. This is because many products in Adventureworks database are not assigned any ProductSubCategory. Hence, the join between product.ProductSubCategoryId and ProductSubCategory.ProductSubCategoryId fails.
Example b:
,,ASUSAK,Adventureworks,Y
,,ASUSAK,Lab1,Y
c:\temp,N,Y,N
1Production.ProductCategory,user1.cat
2Production.ProductSubcategory,user1.subcat
3Production.Product,user1,prodIn the above example, the hierarchy is established as Category, Subcategory, and Product tables. The data will be exported into user1.cat, user1.subcat, and user1.prod tables. Old tables will not be dropped. New tables will be created if they do not exist. Identity columns will be re-created if the target tables' primary key columns were set up as identity.
Example c:
,,ASUSAK,Adventureworks,Y
,,ASUSAK,Lab1,Y
c:\temp
1Production.ProductCategory,,"[productcategory].name='Bikes'"
2Production.ProductSubcategory
3Production.ProductIn the above example, the hierarchy is established as Category, Subcategory, and Product tables. The data will be exported into dbo._xsProductCategory, dbo._xsProductSubcategory, and dbo._xsProduct tables. Only rows with Production.productcategory.names='Bikes' will be exported.
Tip: How to extract tables which do not have any relationship with each other
Create a dummy table with one row. Make this table as the root table (level 1). You can pick any column name(s) in the dummy table. There is no need to create any constraints either. That's it.
,,ASUSAK,Adventureworks,Y
,,ASUSAK,Lab1,Y
c:\temp
1dummy
2production.category
3production.SubCategory
2HumanResources.DepartmentIn the above example, dummy table will be imported as _xsDummy. All rows of Production.Category will be imported as dbo.category. All related rows of production.SubCategory will be imported as dbo.Subcategory. All rows of HumanResources.Department will be imported as dbo.Department.
Gotchas
- I did not have time to put any error checking. If the script bombs, you will need to figure out what went wrong. Most of the time, it is due to an error in the specification file.
- The import may still fail because your lowest level child may still have links to parent(s), which you are not importing.
- The parent child relationship is picked up from the foreign key constraint in the database. If the constraint does not exist, you will get a Cartesian product for the child table.
- If the data being brought is setup as relational in the specification file then only related rows will be brought over.
- If the target tables are created automatically by BulkImport, the order of columns may change a little. Unfortunately, there is no way to correct this behavior. If the tables have been created before hand then this issue is irrelevant.
- The XSD schema generated by this utility, and the XML generated by bcp export are not indented properly. I recommend a wonderful tool, Free XML Editor from FirstObject.com. Load the file in the editor and press F8, and you will see wonderfully indented XML file.
Final Note
Folks, there is a lot to be desired in this utility. Please give me constructive suggestions to make this utility better.
I have already added the following to my next version:
- Re-adjust the foreign keys in imported items where parents were not included in the export. Of course, this is not as easy as it sounds. I am looking for some input from you.
- Add some error handling. It is kind of low in my priority list, but it must be done.
