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:

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 (http://blog.sqlauthority.com)

About these ads

10 thoughts on “SQL SERVER – How to export data from SQL Server to Excel or any other Format?

  1. 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.

  2. 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

  3. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s