SQL Server database table query results exported as Excel files

I'm sure you'll often encounter problems exporting SQL Server query results to Excel. If you export fewer times, direct "Save Results as ..." Just

1.1 Prepare the query statement.

1.2 Select the database, start the Import and Export Wizard

1.3 Select a data source

1.4 Select target

1.5

1.6

1.7

1.8

The next step is no longer attached to the picture, always point to the "further" button.

2, but when you want to sample each table separately, it is quite troublesome. Today to provide you with a detached Office components can be exported statements results to Excel process, I hope it will help!

---Export to excel

---use instructions:

--1. The server to which you are connecting at execution time determines which server the file resides on

--2. In the remote query statement, add the database name

ALTER PROC Exportfile

@QuerySql VARCHAR (max)

, @Server VARCHAR (20)

, @User VARCHAR (20)

, @Password VARCHAR (20)

, @FilePath NVARCHAR = ' C:exportfile.xls '

As

DECLARE @tmp VARCHAR = ' [# #Table ' + CONVERT (VARCHAR), NEWID ()) + '] '

BEGIN TRY

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

--Determine if the remote server

IF @Server <> '. ' and @Server <> ' 127.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 ', ' in ' + @tmp + ' from ' + @DataSource)

PRINT @Sql

EXEC (@Sql)

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

SELECT @Columns = @Columns + ', ' + name + ' '--Get column name (xp_cmdshell export file has no column name)

, @Data = @Data + ', Convert (nvarchar,[' + name + ']) '--Update the field that contains the result set 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 xp_cmdshell bcp command to export data

EXEC sp_configure ' xp_cmdshell ', 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_cmdshell ', 0

Reconfigure

EXEC (' DROP TABLE ' + @tmp)

End TRY

BEGIN CATCH

--Handling exceptions

IF object_id (' tempdb ... ') + @tmp) is not NULL

EXEC (' DROP TABLE ' + @tmp)

EXEC sp_configure ' xp_cmdshell ', 0

Reconfigure

SELECT Error_message ()

End CATCH

Do not rush to use, this version is based on xp_cmdshell, because to create a file, so to ensure that your users have the right to file management, usually a simple way is to set the startup user of SQL Server to the local system user

Okay, now let's do a look:

--which server the Query Analyzer connects to, and which server the file is on

--Local export

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 = ' 192.168.1.52 ',--varchar (20)

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

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

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

The results are as follows, showing the number of exported bars, there is no error, and then look at your C disk, more than 2 files are done: