SQL SERVER – How to export data from SQL Server to Excel or any other Format?

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:

SQL SERVER - How to export data from SQL Server to Excel or any other Format? img01

Clicking on the Export Data command opens a wizard, in which a file format can be selected:

SQL SERVER - How to export data from SQL Server to Excel or any other Format? img02

Depending on a selected format, there can set advanced options. For example, when exporting to Excel or PDF, you can select a worksheet layout:

SQL SERVER - How to export data from SQL Server to Excel or any other Format? img03

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:

SQL SERVER - How to export data from SQL Server to Excel or any other Format? img04

To export not all the data, you can set a required row range:

SQL SERVER - How to export data from SQL Server to Excel or any other Format? img05

As a result, at the final step, the wizard will create a finished file:

SQL SERVER - How to export data from SQL Server to Excel or any other Format? img06

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)

Previous Post
SQL SERVER – 3 Quick Videos on Careers, Certifications and Information
Next Post
SQL SERVER – vCPUs – How Many Are Too Many CPU for SQL Server Virtualization ? – Notes from the Field #003

Related Posts

No results found.

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.

    Reply
  • Pinal : Is dbforge freeware or there is a license for it?

    Reply
  • 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.

    Reply
  • Oh Pinal, its wonder
    Today, I want this code to generate html for random table and I got it from you…

    Thanks.

    Reply
  • 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

    Reply
  • hi, anyone can help me doing this with SSIS? dbforge isnt free

    Reply
  • thanks for the post. “dbforge isnt free” – try the express version, it is fully free.

    Reply
  • 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

    Reply
  • Thanks a lot

    Reply
  • Hello,
    How can Live Excel data(data here changes every second)be imported into SQL Server?

    Reply
  • “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.

    Reply
  • 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

    Reply
  • Import Wizard works great, but need automation and we are using the Express version.

    Reply
  • Thank u soooo much sir,… this software helped a lot.. n saved lots of tym tooo.. :-)

    Reply
  • Hi Pinal Dave..Through SQL Script how to create excel in path and export data to excel sheet..Will it work OPENROWSET ?

    Reply

Leave a Reply

Menu