How to export data from a SQL Server table to HTML

Views: 4.076
Reading Time: 5 minutes

Dear,
Good afternoon.

In this post I will demonstrate how to export data from a SQL Server table to an HTML file or a variable of type VARCHAR, where two very similar Procedures will be presented, where one generates the HTML as a physical file on disk and the other writes the HTML generated in an OUTPUT variable.

I also made a post about this same functionality, but using the CLR, which allows exporting a query to HTML faster and more practical than using OLE Automation. If you want to know more, visit the post SQL Server - How to Email a Query Result in HTML Format Using CLR (C #).

Prerequisites

If you only need to use the procedure where HTML is returned as an OUTPUT variable, you can skip the prerequisites.

For the procedure where a physical file is generated on disk, I need you to have created the procedure stpWrite_File_Filewhich I talked about her in the post File Operations Using OLE Automation in SQL Server.

Remember that to use OLE Automation, this feature must be enabled on the server. If not enabled, see the post Enabling OLE Automation via T-SQL on SQL Server how to do this.

Creating a test data mass

Returning HTML as an OUTPUT variable

This functionality should be used when you need to handle or perform some action with the generated HTML, such as sending an email with the data in this table, for example.

Example of use:

Example usage with centered text and sorting results by TABLE_NAME column:

Source code
View source

Final result:
SQL Server x HTML2

Exporting HTML to File

This functionality should be used when you need to generate a physical file on disk or network containing the contents of a table in HTML format.

Example of use:

Source code
View source

Final result:
SQL Server x HTML

That's it, guys.
To the next!