SQL Server database table query results exported as Excel files

Source: Internet
Author: User
Tags sql server query

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:

Related Article

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.