Export the SQL Server database table query result as an excel file

Source: Internet
Author: User
Tags sql server query
I believe that you will often encounter the problem of exporting SQL Server query results to Excel. If the number of export times is small, SaveResultsAs directly... 1.1 prepare the query statement 1.2 select the database, start the Import and Export wizard 1.3 select the data source 1.4 select the target 1.51.61.71.8 and the subsequent steps will not be shown, just click the next button.

I believe that you will often encounter the problem of exporting SQL Server query results to Excel. If the number of export times is small, Save Results... 1.1 prepare the query statement 1.2 select the database, start the Import and Export wizard 1.3 select the data source 1.4 select the target 1.5 1.6 1.7 follow-up steps are not shown, just click the next button.

I believe that you will often encounter the problem of exporting SQL Server query results to Excel. If the number of export times is small, simply "Save Results;

1.1 prepare the query statement

1.2 select a database and start the Import and Export wizard

1.3 select a data source

1.4 select a target

1.5

1.6

1.7

1.8

Click "Next" until the next step is displayed.

2. But it is quite troublesome to sample each table separately. Today, we will provide you with a process of exporting statement results to Excel without the office component. I hope it will be helpful to you!

--- Export to Excel

--- Usage instructions:

-- 1. The server connected during execution determines the server on which the file is stored.

-- 2. Add the database name to the remote query statement.

Alter proc ExportFile

@ QuerySql VARCHAR (max)

, @ Server VARCHAR (20)

, @ User VARCHAR (20)

, @ Password VARCHAR (20)

, @ FilePath NVARCHAR (100) = 'C: ExportFile.xls'

AS

DECLARE @ tmp VARCHAR (50) = '[# table' + CONVERT (VARCHAR (36), NEWID () +']'

BEGIN TRY

DECLARE @ SQL VARCHAR (max), @ DataSource VARCHAR (max) = '';

-- Determine whether the server is a remote server

IF @ Server <> '.' AND @ Server <> '2017. 0.0.1'

SET @ DataSource = 'opendatasource (''sqloledb'', ''data Source = '+ @ Server +'; User; Password = '+ @ Password + ''').'

-- Export the result set to the specified database

SET @ SQL = REPLACE (@ QuerySql, 'from', 'input' + @ tmp + 'from' + @ DataSource)

PRINT @ SQL

EXEC (@ SQL)

DECLARE @ Columns VARCHAR (max) = '', @ Data NVARCHAR (max) =''

SELECT @ Columns = @ Columns + ', ''' + name + ''' -- get the column name (the xp_export shell file has no column name)

, @ Data = @ Data + ', Convert (Nvarchar, [' + name + ']) '-- update the field where the result set is located to nvarchar (avoid type conflicts when column names and data union)

FROM tempdb. sys. columns WHERE object_id = OBJECT_ID ('tempdb .. '+ @ tmp)

SELECT @ Data = 'select' + SUBSTRING (@ Data, 2, LEN (@ Data) + 'from' + @ tmp

SELECT @ Columns = 'select' + SUBSTRING (@ Columns, 2, LEN (@ Columns ))

-- Use the bcp command of xp_cmdshell to export data

EXEC sp_configure 'xp _ Your shell', 1

RECONFIGURE

DECLARE @ cmd NVARCHAR (4000) = 'bcp "'+ @ Columns + 'Union all' + @ Data +'" queryout '+ @ FilePath +'-c-t'

PRINT @ cmd

Exec sys. xp_cmdshell @ cmd

EXEC sp_configure 'xp _ Your shell', 0

RECONFIGURE

EXEC ('drop table' + @ tmp)

END TRY

BEGIN CATCH

-- Handle exceptions

IF OBJECT_ID ('tempdb .. '+ @ tmp) IS NOT NULL

EXEC ('drop table' + @ tmp)

EXEC sp_configure 'xp _ Your shell', 0

RECONFIGURE

SELECT ERROR_MESSAGE ()

END CATCH

Do not worry about using it. This version is based on xp_cmdshell. To create a file, ensure that your users have the permission to manage the file, generally, the simple method is to set the SQL server startup user as a local system user.

Now let's take a look:

-- Query the server on which the analyzer connects and where the file is located

-- Export locally

EXEC dbo. ExportFile @ QuerySql = 'select * from sys. objects', -- varchar (max)

@ Server = '.', -- varchar (20)

@ FilePath = n'c: objects.xls '-- nvarchar (100)

-- Remote export

EXEC dbo. ExportFile @ QuerySql = 'select * from master. sys. objects', -- varchar (max)

@ Server = '1970. 168.1.52 ', -- varchar (20)

@ User = 'sa ', -- varchar (20)

@ Password = 'sa ', -- varchar (20)

@ FilePath = n'c: 52objects.xls '-- nvarchar (100)

The execution result is as follows. If the number of exported items is displayed, no error is reported. Check your drive C and the result is as follows:

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.