SQL Server BCP Command Example for SQL Output to File
In this sql tutorial, t-sql developers will find MS SQL BCP example to write SQL output to file.
Using SQL BCP command, developers can write output to text file.
Here below t-sql developers can find the basic sql BCP command syntax.
| usage: 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"] | [-x generate xml format file] | |
| [-d database name] | ||
SQL programmers can write the contents of a SQL Server table as sql output to file on a Windows folder.
Or they can write the results of execution of a sql query as sql output to text file on the disk.
The {dbtable | query} syntax is referring to that function.
Also parallel to database table or query results as out | queryout is used in the BCP command.
SQL Server database administrators should enable xp_cmdshell extended stored procedure before running the below sample.
Otherwise "SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell'" error will be displayed.
Below, t-sql developers can find a t-sql bcp command example where the return set of a SQL select query is written as sql output to text file.
The names of people stored in Person table in SQL AdventureWorks2008R2 database are used for sql output to file named "textfile.txt" which will be created at root of C drive.
The file has UTF8 encoded and the trusted connection is used between the Windows system and the SQL Server database.
The server is pointed as localhost where the BCP command is executed on.
If you are executing this BCP command example on a named instance rather than default instance, you should replace the "localhost" with "servername\instancename"
For example instead of "-Slocalhost" use "-SKodyaz\Denali"
Please check the [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name error for details.
DECLARE @cmd varchar(1000)
SET @cmd = 'bcp "SELECT FirstName, LastName FROM AdventureWorks2008R2.Person.Person" queryout "c:\textfile.txt" -c -UTF8 -T -Slocalhost'
EXEC master..xp_cmdshell @cmd
Another SQL BCP command example that can be used to write a sql variable as output to text file is as follows.
The sample sql code has a varchar variable which stores the data to sql output to file in the xp_cmdshell bcp script.
Note that since we build dynamic sql for xp_cmdshell command string, we replace the "'" with double ones, "''"
declare @v varchar(1000) = N'Here''s an example'
DECLARE @cmd VARCHAR(1000)
SET @cmd = 'bcp "select ''' + replace(@v,'''','''''') + '''" queryout "c:\textfile.txt" -c -UTF8 -T -Slocalhost"'
EXEC master..xp_cmdshell @cmd
The above SQL Server xp_cmdshell and SQL bcp command examples have some limitations.
But this method seems to be the easiest way to sql output to file from SQL Server database for basic cases.