In SQL Server, you can invoke a DOS command-line tool bcp to generate a text file for either the data in the table or the SQL statement results.
Parameter format for bcp command:
Copy Code code as follows:
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"]
But the operation below the command line is not very flexible in passing variables, I refer to csdn Li Jinjian Write file2table example,
Write a system stored procedure that generates a text file from the SQL statement result Sp_sql_query_to_file.sql:
Copy Code code as follows:
/***************************************************************************
Note: Generate text files based on SQL statements (with date stamps, fields separated by commas)
All occurrences of the table name in the SQL statement are to be in the database name. [User name]. [table name] to mark the details
Created: Maggiefengyu Mail: maggiefengyu@tom.com Date: 2006-03-03
***************************************************************************/
Create PROCEDURE Sp_sql_query_to_file (
@servername varchar (50),--server name
@username varchar (50)--user name, NULL if NT authentication method
@password varchar (50),--Password
@sql_query varchar,--sql query statement, with single quotes when character conditions are used
@filePath_name varchar (1000),--Export the path and file name
@date datetime-The build date stamp when the file is saved
) as
DECLARE @sql varchar (8000)
SET @sql = ' bcp ' + @sql_query + ' "queryout ' + '" ' + @filePath_name + ' _ ' +
CONVERT (varchar, @date, 112) + '. txt '/t ', '/w '
+ '/S ' + @servername
+case when ISNULL (@username, ') = ' THEN ' ELSE '/u ' + @username end
+ '/P ' +isnull (@password, ')
Select @sql
EXEC Master.. xp_cmdshell @sql
IF @ @Error <>0 BEGIN
print ' generates file error, please check! ' +@ @Error
End
Go
