Note: If you want to practice the code and the example along with here, you can download dbForget Studio from here.
Everyone, who is involved in work with databases, eventually faces with data export/import tasks. Unlike data import tasks, data export has to be performed in various formats, depending on the business requirements of customers. SSMS capabilities allow saving selection result in the CSV format or as a text file with delimiters. Moreover, SQL Server supports the OPENROWSET construct, which usage enables export to XLS files. However, this functionality was scarcely enough for me to perform current tasks. In order to resolve the situation and meet customers’ requirements, I had to automate the export process using dynamic SQL.
At first, such an approach justified itself. For example, here is an HTML markup generating script for a random table/view:
USE AdventureWorks2012;
DECLARE
@object_name SYSNAME
, @object_id INT
, @record_count INT
, @how_extended_properties BIT
SELECT
@object_name = '[Person].[Password]'
, @object_id = OBJECT_ID(@object_name)
, @record_count = 100
, @how_extended_properties = 0
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = 'SELECT CAST((SELECT ''<table border="1" ><tr>'' + ' +
STUFF((
SELECT ' + ''<th>' +
CASE WHEN @how_extended_properties = 1
THEN ISNULL(c.name + ' (' + CAST(e.value AS NVARCHAR(100)) + ')', c.name)
ELSE c.name
END + '</th>'''
FROM sys.columns c WITH(NOWAIT)
LEFT JOIN sys.extended_properties e WITH(NOWAIT) ON e.major_id = c.[object_id]
AND e.minor_id = c.column_id
AND e.class = 1
WHERE TYPE_NAME(c.system_type_id) NOT IN ('xml', 'geography', 'geometry', 'hierarchyid')
AND c.[object_id] = @object_id
ORDER BY c.column_id
FOR XML PATH, TYPE).value('.', 'NVARCHAR(MAX)'), 1, 3, '')
+ ' + ''</tr>'' + (
SELECT TOP ' + CAST(@record_count AS VARCHAR(10)) + ' ''<tr>'' + ' +
STUFF((
SELECT '+ ''<td>''' +
CASE
WHEN TYPE_NAME(c.system_type_id) = 'uniqueidentifier'
THEN ' + ISNULL('''' + CAST([' + c.name + '] AS VARCHAR(MAX)) + '''', '''')'
WHEN TYPE_NAME(c.system_type_id) IN ('nvarchar', 'nchar')
THEN ' + ISNULL('''' + CAST(REPLACE([' + c.name + '], '''', '''''''') AS NVARCHAR(MAX)) + '''', '''')'
WHEN TYPE_NAME(c.system_type_id) IN ('varchar', 'char', 'varbinary', 'binary')
THEN ' + ISNULL('''' + CAST(REPLACE([' + c.name + '], '''', '''''''') AS VARCHAR(MAX)) + '''', '''')'
WHEN TYPE_NAME(c.system_type_id) IN ('datetime', 'time', 'date')
THEN ' + ISNULL('''' + CONVERT(VARCHAR, [' + c.name + '], 120) + '''', '''')'
ELSE ' + ISNULL(CAST([' + c.name + '] AS NVARCHAR(MAX)), '''')'
END + ' + ''</td>'''
FROM sys.columns c WITH(NOWAIT)
WHERE TYPE_NAME(c.system_type_id) NOT IN ('xml', 'geography', 'geometry', 'hierarchyid')
AND c.[object_id] = @object_id
ORDER BY c.column_id
FOR XML PATH, TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + '+ ''</tr>''
FROM ' + @object_name + '
FOR XML PATH, TYPE).value(''.'', ''NVARCHAR(MAX)'') + ''</table>'' ) AS XML)'
PRINT @SQL
EXEC sys.sp_executesql @SQL
But after a while I was asked to generate export to Excel with a custom formatting – certainly, SQL wouldn’t cope with such a task. After a brief search, I found the advanced functionality to export in various formats using export and import tools embedded in dbForge Studio for SQL Server. And since then I have used it exclusively to perform such tasks.
In dbForge Studio you can export all data from a specified table/view or export selected rows from a custom query:
Clicking on the Export Data command opens a wizard, in which a file format can be selected:
Depending on a selected format, there can set advanced options. For example, when exporting to Excel or PDF, you can select a worksheet layout:
If necessary, you can select columns, from which data is to be exported. Thus, there is an option to specify custom names and types for columns:
To export not all the data, you can set a required row range:
As a result, at the final step, the wizard will create a finished file:
In order not to set option every time, an export template can be created.
As you can see, dbForge Studio for SQL Server has a wide range of features allowing exporting data in various formats. You can download dbForget Studio from here.
Reference: Pinal Dave (https://blog.sqlauthority.com)
