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

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: