I just posted a template in the CodeSmith Template Share forum that uses XML with sp_xml_preparedocument, OPENXML, and sp_xml_removedocument to accurately script out data in a SQL Server table.

Some highlights of the template:
  • The original table data is copied into a new table for backup.
  • If a TargetDatabase is defined, the script will include a "Use" statement.
  • If a TargetDatabase is defined, and the AutoExecutescript property is true, the ouput will be executed on the target database
  • If the current table has an identity column, the seed is maintained.
  • The entire operation is handled in a Serialized Transaction.
This template was built and tested with CodeSmith 3.0

Check it out, and give me some feedback...  how does it compare to the template that ships with CodeSmith?  Anyone got any ideas for enhancing this template to handle Binary data?

tc