The SQL Server database table query result is exported 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, 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 ID = '+ @ 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: