This project is read-only.

Documentation For SQL CLR Export


There are 20 total CLR procedures that are exposed in this project.
This CLR package consists of 4 core functionalities.
The CLR functions would have access to Any regular table, CTE, Temp Table or Table Variable the calling code has access to due to the scope of the transaction.

The code is tolerant of the file paths having or missing slashes between the filename and the filepath; it figures out what it should be via an internal to the code function.
In some cases, the logic is repetitively redundant, but is included to help people visualize the work being done. for example, what if you append to an empty file? well it gets created automatically. That could be a Parameter on the original procedure, instead of a stand alone proc!
Why not a Parameter to decide CSV/TSV/Custom, instead of separate functions? Same thing...it lets people think a certain path, even though there's other ways to do the same thing.
  • Export Table to (Some Format)
  • Export Query Results to (Some Format)
  • Append Table to an Existing File (Some Format)
  • Append Query Results to an Existing File (Some Format)

Nothing Beats a copy/Paste model you can Adapt!

Specific Examples of the usage are below:
  • CLR_ExportTableTo...
  • the HTML Export has a lot of options.
jump to HTML Export Details
  • the @Title parameter allows you to assign a title to the html page (the <title> tag) via .
  • the @Summary parameter allows you to place a descriptive paragraph, explanation, or other information at the top of the document.
  • the @HTMLStyle accepts an interger expecting a value between 1 and 35, which in turn uses a matching CSS style in the document to change the colors /appearance of the document that gets created. Integer values outside of the expected range gets defaulted to a specific style, which happens to be my favorite at the moment.

 EXECUTE CLR_ExportTableToHTML @TableName = '#Results',
                              @FilePath = 'C:\Data\',
                              @FileName = '\Results_export.txt',
                              @IncludeHeaders = 1
                              @Title = 'Page Title',
                              @Summary = 'This is my Summary Description For  All The business Information you Requested',
                              @HTMLStyle = 1

Other Export Examples:
 EXECUTE CLR_ExportTableToCSV @TableName = 'Products',
                              @FilePath = 'C:\Data',
                              @FileName = 'Products_export.csv',
                              @IncludeHeaders = 1

 EXECUTE CLR_ExportTableToTSV @TableName = '#Results',
                              @FilePath = 'C:\Data',
                              @FileName = 'Products_export.tsv',
                              @IncludeHeaders = 0

 EXECUTE CLR_ExportTableToCustomDelim @TableName = '@TableVariable',
                              @FilePath = 'C:\Data\',
                              @FileName = '\Results_export.txt',
                              @IncludeHeaders = 1,
                              @CustomDelimiter = '[~~]'

 EXECUTE CLR_ExportTableToFixedWidth @TableName = '[Another With Spaces In The Name]',
                              @FilePath = 'C:\Data\',
                              @FileName = '\Results_export.txt',
                              @IncludeHeaders = 1

 EXECUTE CLR_ExportTableToXML @TableName = '#Results',
                              @FilePath = 'C:\Data\',
                              @FileName = '\Results_export.txt'

  • CLR_ExportQueryTo...
Except for the option to Export to XML, the functionality here is identical to the CLR_ExportTableTo.. items above. Same Parameters, same layout, same behind the scenes work.
More Advanced Examples of the usage are below for a Quick Reference:
  •  EXECUTE CLR_ExportQueryToHTML @QueryCommand = 'SELECT myAlias.* FROM #Results myAlias WHERE SomeColumn = 1',
                                  @FilePath = 'C:\Data\',
                                  @FileName = '\Results_export.txt',
                                  @IncludeHeaders = 1
                                  @Title = 'Page Title',
                                  @Summary = 'This is my Summary Description For  All The business Information you Requested',
                                  @HTMLStyle = 1

    Other Export Examples:
     EXECUTE CLR_ExportQueryToCSV @QueryCommand = 'SELECT * FROM Products',
                                  @FilePath = 'C:\Data',
                                  @FileName = '\Products_export.csv',
                                  @IncludeHeaders = 1

     EXECUTE CLR_ExportQueryToTSV @QueryCommand = 'SELECT * FROM #Results',
                                  @FilePath = 'C:\Data',
                                  @FileName = 'Products_export.tsv',
                                  @IncludeHeaders = 0

     EXECUTE CLR_ExportQueryToCustomDelim @QueryCommand = 'SELECT * FROM @TableVariable',
                                  @FilePath = 'C:\Data\',
                                  @FileName = '\Results_export.txt',
                                  @IncludeHeaders = 1,
                                  @CustomDelimiter = '[~~]'

     EXECUTE CLR_ExportQueryToFixedWidth @QueryCommand = 'SELECT * FROM [Another With Spaces In The Name]',
                                  @FilePath = 'C:\Data\',
                                  @FileName = '\Results_export.txt',
                                  @IncludeHeaders = 1

     EXECUTE CLR_ExportQueryToXML @QueryCommand = 'SELECT * FROM #Results',
                                  @FilePath = 'C:\Data\',
                                  @FileName = '\Results_export.txt'


    !!Append Queries cannot do HTML or XML
    So if you need to append results together, use a query featuring UNION or insert the results into a temp table and export them all at once
    • CLR_ExportAppendTableTo...
    Except for the option to Export to XML, the functionality here is identical to the items above. Same Parameters, same layout, same behind the scenes work. The file you select does not have to exist, but if it DOES, the new results are simply appended to the bottom of the existing results.
     EXECUTE CLR_ExportTableAppendToCSV @TableName = 'Products',
                                  @FilePath = 'C:\Data',
                                  @FileName = 'Products_export.csv',
                                  @IncludeHeaders = 1

     EXECUTE CLR_ExportTableAppendToTSV @TableName = '#Results',
                                  @FilePath = 'C:\Data',
                                  @FileName = 'Products_export.tsv',
                                  @IncludeHeaders = 0

     EXECUTE CLR_ExportTableAppendToCustomDelim @TableName = '@TableVariable',
                                  @FilePath = 'C:\Data\',
                                  @FileName = '\Results_export.txt',
                                  @IncludeHeaders = 1,
                                  @CustomDelimiter = '[~~]'

     EXECUTE CLR_ExportTableAppendToFixedWidth @TableName = '[Another With Spaces In The Name]',
                                  @FilePath = 'C:\Data\',
                                  @FileName = '\Results_export.txt',
                                  @IncludeHeaders = 1

    • CLR_ExportAppendQueryTo...
    Except for the option to Export to XML, the functionality here is identical to the items above. Same Parameters, same layout, same behind the scenes work. The file you select does not have to exist, but if it DOES, the new results are simply appended to the bottom of the existing results.

     EXECUTE CLR_ExportQueryAppendToCSV @QueryCommand = 'SELECT * FROM Products',
                                  @FilePath = 'C:\Data',
                                  @FileName = '\Products_export.csv',
                                  @IncludeHeaders = 1

     EXECUTE CLR_ExportQueryAppendToTSV @QueryCommand = 'SELECT * FROM #Results',
                                  @FilePath = 'C:\Data',
                                  @FileName = 'Products_export.tsv',
                                  @IncludeHeaders = 0

     EXECUTE CLR_ExportQueryAppendToCustomDelim @QueryCommand = 'SELECT * FROM @TableVariable',
                                  @FilePath = 'C:\Data\',
                                  @FileName = '\Results_export.txt',
                                  @IncludeHeaders = 1,
                                  @CustomDelimiter = '[~~]'

     EXECUTE CLR_ExportQueryAppendToFixedWidth @QueryCommand = 'SELECT * FROM [Another With Spaces In The Name]',
                                  @FilePath = 'C:\Data\',
                                  @FileName = '\Results_export.txt',
                                  @IncludeHeaders = 1


    More Advanced Examples of the usage are below for a Quick Reference:

Last edited May 4, 2012 at 8:04 PM by lizaguirre, version 20

Comments

No comments yet.