Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how SQL Server uses the bcp command to generate the results of SQL statements into text files". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In SQL Server, you can call the command line tool bcp under DOS to generate a text file of the data in the table or the result of the SQL statement. The parameter format of the BCP command: the copy code is as follows: BCP {dbtable | query} {in | out | 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 following operation on the command line is not very flexible in passing variables. I refer to the example of Zou Jian writing File2Table in CSDN. A system stored procedure sp_sql_query_to_file.sql that generates a text file from the result of the SQL statement is written: the copy code is as follows: / * * * / / description: generate a text file (with date stamp) according to the SQL statement Fields are separated by commas) / / all table names that appear in the SQL statement should use the database name. [user name]. [table name] to mark / / create: maggiefengyu email: maggiefengyu@tom.com date: 2006-03-03 * * / create PROCEDURE sp_ Sql_query_to_file (@ servername varchar (50) -- server name @ username varchar (50),-- user name, if NT authentication, empty'@ password varchar (50),-- password @ sql_query varchar (2000),-- SQL query statement, single quotation mark @ filePath_name varchar (1000) if there are character conditions -- Export path and file name @ date datetime-- the generation date stamp when saving the file) AS DECLARE @ sql varchar (8000) SET @ sql='bcp "'+ @ sql_query +'" queryout'+ ""'+ @ filePath_name+'_'+ convert (varchar (12), @ 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 @ @ Error0 BEGIN print'an error occurred while generating the file Please check!'+ @ @ Error END GO

This is the end of the content of "how SQL Server uses the bcp command to generate a text file for the results of SQL statements". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.