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)
18 Comments. Leave new
This can also be done using SSIS. In fact if you Export a table through SSMS by right clicking it gives you the option to save the result as an SSIS package.
Pinal : Is dbforge freeware or there is a license for it?
It’s a very nice looking tool, but I would tend to use the “Results To File” option in SSMS and open the resultant .csv file in Excel.
Oh Pinal, its wonder
Today, I want this code to generate html for random table and I got it from you…
Thanks.
Hello
good tutorial indeed but we have life saving tool called SSIS
we can use it and in a matter of 3 steps, we have our data in Excel or another desired format
Best Regards
Cheers
hi, anyone can help me doing this with SSIS? dbforge isnt free
thanks for the post. “dbforge isnt free” – try the express version, it is fully free.
Hi Pinal
I have created procedure using your code, in which just need to pass tablename as a parameter and it will return html for mail or other purpose…
—————————————————————
create procedure [dbo].[spGetHTML]
@object_name nvarchar(100), –pass table or view name
@columns nvarchar(max) = null, –null for all columns
@where nvarchar(max) = null, –null for all records
@html nvarchar(max) output
as
begin
DECLARE @object_id INT
DECLARE @record_count INT
DECLARE @how_extended_properties BIT
DECLARE @SQL NVARCHAR(MAX)
DECLARE @ParmDefinition NVARCHAR(500)
declare @htmlGet xml
SET @ParmDefinition = N’@htmlOUT xml OUTPUT’
set @object_id = OBJECT_ID(@object_name)
set @record_count = 100
set @how_extended_properties = 0
SELECT @SQL =
‘SELECT @htmlOUT = CAST((SELECT ”” + ‘ +
STUFF(( SELECT ‘ + ”’ +
CASE WHEN @how_extended_properties = 1
THEN ISNULL(c.name + ‘ (‘ + CAST(e.value AS NVARCHAR(100)) + ‘)’, c.name)
ELSE c.name
END + ””
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
and (@columns is NULL
OR
c.name in (select ltrim(rtrim(Strings)) FROM dbo.split(@columns,’,’))
)
ORDER BY c.column_id
FOR XML PATH, TYPE
).value(‘.’, ‘NVARCHAR(MAX)’), 1, 3, ”
)
+ ‘ + ”” + (SELECT TOP ‘ + CAST(@record_count AS VARCHAR(10)) + ‘ ”” + ‘
+ STUFF((SELECT ‘+ ””’ +
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 + ‘ + ””’
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
and (@columns is NULL
OR
c.name in (select ltrim(rtrim(Strings)) FROM dbo.split(@columns,’,’))
)
ORDER BY c.column_id
FOR XML PATH, TYPE
).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, ”
) + ‘+ ””
FROM ‘ + @object_name + ‘
where 1 = 1 ‘ + case when @where IS NOT NULL then ‘ ‘ + @where ELSE ” END +
‘ FOR XML PATH, TYPE).value(”.”, ”NVARCHAR(MAX)”) + ”” ) AS XML)’
–select @SQL
EXEC sys.sp_executesql @SQL, @ParmDefinition, @htmlOUT = @htmlGet OUTPUT
IF LEN(cast(@htmlGet as nvarchar(max))) > 0
select @html = cast(@htmlGet as nvarchar(max))
end
Thanks a lot
Hello,
How can Live Excel data(data here changes every second)be imported into SQL Server?
“dbforge isnt free” – try the express version, it is fully free……..
The express version is fully free but it will restrict your export to only 50 records or 1000 depending on your output format. To get all you result out of your export I’m afraid you still have to pay. This has been my experience, I don’t know about others.
It is a very sleek product though.
Hi .. i have a doubt that when i try to export the data from sql 2008 express edition t o excel , im getting the error like it is not possible in the express edition. is there any other method to export the data
You can use Import Export Wizard which is available with SQL Sevrer Management Studio.
Import Wizard works great, but need automation and we are using the Express version.
You can use SMO.
Thank u soooo much sir,… this software helped a lot.. n saved lots of tym tooo.. :-)
Thanks for letting us know Pree
Hi Pinal Dave..Through SQL Script how to create excel in path and export data to excel sheet..Will it work OPENROWSET ?