Generate a text file for SQL statement results in SQL Server

In SQL Server, you can call the command line tool BCP under DOS to generate text files for the data or SQL statement results in the table.

BCP command parameter format:

BCP {dbtable | query} {in | out | queryout | format} datafile[-m maxerrors]　　　　　　[-f formatfile]　　　　　[-e errfile][-F firstrow]　　　　　　 [-L lastrow]　　　　　　 [-b batchsize][-n native type]　　　　　[-c character type]　　　[-w wide character type][-N keep non-text native] [-V file format version] [-q quoted identifier][-C code page specifier]　[-t field terminator]　　[-r row terminator][-i inputfile]　　　　　　[-o outfile]　　　　　　 [-a packetsize][-S server name]　　　　　[-U username]　　　　　　[-P password][-T trusted connection]　 [-v version]　　　　　　 [-R regional enable][-k keep null values]　　 [-E keep identity values][-h "load hints"]

However, the operations below the command line are not flexible in transferring variables. I refer to ZOU Jian's example of writing file2table in csdn,

Wrote a system stored procedure for generating text files based on SQL statement results: sp_ SQL _query_to_file. SQL:

/*************************************** *********************************** // Description: generate text files based on SQL statements (with a date stamp, fields are separated by commas) // all the table names in SQL statements must use the [database name]. [user name]. [Table name] to mark in detail // create: maggiefengyu mail: maggiefengyu@tom.com Date: *************************************** * *********************************/create procedure sp_ SQL _query_to_file (@ servername varchar (50 ), -- server name @ username varchar (50), -- user name. If you use the NT authentication method Null ''@ password varchar (50), -- password @ SQL _query varchar (2000), -- SQL query statement. If there are character conditions, use single quotes @ filepath_name varchar (1000 ), -- export path and file name @ date datetime -- date stamp generated when saving the file) asdeclare @ SQL varchar (8000) set @ SQL = 'bcp "'+ @ SQL _query +'" queryout '+' "'{@filepath_name='_'{convert(varchar(12,@date,{{'.txt"/T ", "/W' + '/s' + @ servername + case when isnull (@ username ,'') = ''then'' 'else'/U' + @ username end + '/P' + isnull (@ pass Word, '') Select @ sqlexec master .. xp_cmdshell @ sqlif @ error <> 0 ininprint 'An error occurred while generating the file. Please refer! '+ @ Errorendgo

Turn: http://www.bianceng.cn/database/SQLServer/201108/28621.htm