{"id":184,"date":"2006-11-20T00:00:00","date_gmt":"2006-11-20T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/creating-csv-files-using-bcp-and-stored-procedures\/"},"modified":"2026-03-18T12:42:39","modified_gmt":"2026-03-18T12:42:39","slug":"creating-csv-files-using-bcp-and-stored-procedures","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/creating-csv-files-using-bcp-and-stored-procedures\/","title":{"rendered":"BCP to CSV: Export SQL Server Data to CSV Files"},"content":{"rendered":"<div id=\"pretty\">\n<p>The fastest way to create CSV files from SQL Server data is the BCP (Bulk Copy Program) utility executed via xp_cmdshell from T-SQL. A basic export command looks like: bcp master..sysobjects out c:\\file.txt -c -t, -T -S [server], where -c sets character mode, -t, sets the comma delimiter, and -T uses Windows authentication. This article covers creating simple CSV exports, handling data that contains commas (using pipe or caret delimiters), adding column headers, and wrapping BCP calls in stored procedures for repeatable exports.<\/p>\n<h2>How to create a simple CSV file<\/h2>\n<p>The simplest way to copy data from a database table to file is to use the basic BCP command:<\/p>\n<pre>BCP master..sysobjects out c:\\sysobjects.txt -c -t, -T -S&lt;servername&gt;<\/pre>\n<p>The basic format for the BCP command for creating a CSV file is as follows:<\/p>\n<p><strong>BCP &lt;table&gt; out &lt;filename&gt; &lt;switches&gt;<\/strong><\/p>\n<p>The switches used here are:<\/p>\n<ul>\n<li><span class=\"ImportantWords\">-c<\/span> Output in ASCII with the default field terminator (tab) and row terminator (crlf)<\/li>\n<li><span class=\"ImportantWords\">-t<\/span> override the field terminator with &#8220;,&#8221;<\/li>\n<li><span class=\"ImportantWords\">-T<\/span> use a trusted connection. Note that U -P may be used for username\/password<\/li>\n<li><span class=\"ImportantWords\">-S<\/span> connect to this server to execute the command<\/li>\n<\/ul>\n<p>Note that, like DTS\/SSIS, BCP is a client utility, hence you need to supply the connection information.<\/p>\n<p>For transfer of data between SQL servers, in place of <b>-c<\/b>, use <b>-n<\/b> or <b>-N<\/b> for native data format (<b>-N<\/b> = Unicode). This is much faster and avoids data conversion problems. Please refer to the previous BOL link for the complete format of the BCP command.<\/p>\n<p>As BCP is a command line utility it is executed from T-SQL using xp_cmdshell. Create a directory called <b>BCP<\/b> on your c: drive and execute:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out \n     c:\\bcp\\sysobjects.txt -c -t, -T -S'+             @@servernameexec master..xp_cmdshell @sql<\/pre>\n<h2>Other field and row delimiters<\/h2>\n<p>Often, character data includes commas which will be interpreted in the file as a field terminator. A simple way to cater for this is to use a different field delimiter which does not appear in the data. Commonly used characters are pipe (|):<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out\n       c:\\bcp\\sysobjects.txt -c -t| -T -S' +            @@servernameexecmaster..xp_cmdshell@sql<\/pre>\n<p>And caret (^):<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out\n      c:\\bcp\\sysobjects.txt -c -t^ -T -S' +            @@servernameexec master..xp_cmdshell @sql<\/pre>\n<p>The terminators are not limited to a single character, so if necessary you can use (|^):<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out \n              c:\\bcp\\sysobjects.txt -c -t|^ -T -S' +           @@servernameexec master..xp_cmdshell @sql<\/pre>\n<p>Note that this will increase the size of the file and so slow down the import\/export. Another way to cater for embedded commas is to &#8220;quote encapsulate&#8221; character data &#8211; more about that later.<\/p>\n<p>It is unusual to need to change the row terminator from the default of <strong>crlf<\/strong> (carriage return, line feed) but occasionally you will need the <strong>cr<\/strong> or <strong>lf<\/strong> on its own.<\/p>\n<p>To do this use the hex value <strong>cr = 13 = 0x0D<\/strong>, <strong>lf = 10 = 0x0A<\/strong>. If you can&#8217;t remember these values they are easily obtained, as follows:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">select cr = ascii('')select lf = ascii(right('',1))<\/pre>\n<p>To use these in the BCP command for the row terminator the <strong>-r<\/strong> switch is used:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out \n\u00a0\u00a0\u00a0\u00a0     c:\\bcp\\sysobjects.txt -c -t, -r0x0D -T -S' +            @@servernameexec master..xp_cmdshell @sql<\/pre>\n<p>When the resulting file is opened in notepad the row terminators will not end the line &#8211; the character should appear as a non-ASCII character (a little oblong). If opened or copied to query analyser (or management studio) these will be interpreted as line breaks and the file will be more readable.<\/p>\n<p>The terminator characters can be encapsulated with double quotes in the command to allow for space. I like to do this for anything other than a single character delimiter. The following example gives a crlf row terminator and | ^ field terminator.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">declare @sql varchar(8000)\nselect @sql = 'bcp master..sysobjects out\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c:\\bcp\\sysobjects.txt -c -t\"| ^\" -r\"0x0D0A\" -T -S'\n+ @@servername\nexec master..xp_cmdshell @sql<\/pre>\n<h2>Formatting the extracted data<\/h2>\n<p>If you do not require all the columns\/rows, or you wish to alter the data, a simple method is to create a view. This can be used in the BCP command in the same way as a table. The following view allows you to extract just the name, the formatted create date and time and then order the results according the date created (most recent first):<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">use tempdb\ngo\ncreate view vw_bcpMasterSysobjects\nas\n\u00a0 select top 100 percent\n\u00a0\u00a0\u00a0\u00a0\u00a0 name ,\n\u00a0\u00a0\u00a0\u00a0\u00a0 crdate = convert(varchar(8), crdate, 112) ,\n\u00a0\u00a0\u00a0\u00a0\u00a0 crtime = convert(varchar(8), crdate, 108)\n\u00a0\u00a0 from master..sysobjects\n\u00a0\u00a0 order by crdate desc\ngo\ndeclare @sql varchar(8000)\nselect @sql = 'bcp tempdb..vw_bcpMasterSysobjects out\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c:\\bcp\\sysobjects.txt -c -t, -T -S' + @@servername\nexec master..xp_cmdshell @sql<\/pre>\n<p>Now we can quote encapsulate the extracted data by including the formatting in the view:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">use tempdb\ngo\ncreate view vw_bcpMasterSysobjects\nas\n\u00a0\u00a0 select top 100 percent\n\u00a0\u00a0\u00a0\u00a0\u00a0 name = '\"' + name + '\"' ,\n\u00a0\u00a0\u00a0\u00a0\u00a0 crdate = '\"' + convert(varchar(8), crdate, 112) + '\"' ,\n\u00a0\u00a0\u00a0\u00a0\u00a0 crtime = '\"' + convert(varchar(8), crdate, 108) + '\"'\n\u00a0\u00a0 from master..sysobjects\n\u00a0\u00a0 order by crdate desc\ngo\ndeclare @sql varchar(8000)\nselect @sql = 'bcp tempdb..vw_bcpMasterSysobjects out \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c:\\bcp\\sysobjects.txt -c -t, -T -S' + @@servername\nexec master..xp_cmdshell @sql<\/pre>\n<p><b>Note:<br \/><\/b><i>Quote encapsulation can also be performed by use of a format file.<br \/><\/i><strong><br \/>Read also: <\/strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/building-a-sql-server-data-dictionary\/\">building a SQL Server data dictionary<\/a><i><br \/><\/i><\/p>\n<h2>Complete control &#8211; stored procedures<\/h2>\n<p>If you examined the BCP syntax in BOL, you might have noticed that it is possible to extract from a query by using the <span class=\"CodeInText\">queryout<\/span> keyword in place of <span class=\"CodeInText\">out<\/span>.<\/p>\n<p>So, for example, an equivalent but neater version of the previous code extract would place the <span class=\"CodeInText\">ORDER<\/span> <span class=\"CodeInText\">BY<\/span> clause in the BCP statement rather than the view:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">use tempdb\ngo\ncreate view vw_bcpMasterSysobjects\nas\n\u00a0\u00a0 select\n\u00a0\u00a0\u00a0\u00a0\u00a0 name = '\"' + name + '\"' ,\n\u00a0\u00a0\u00a0\u00a0\u00a0 crdate = '\"' + convert(varchar(8), crdate, 112) + '\"' ,\n\u00a0\u00a0\u00a0\u00a0\u00a0 crtime = '\"' + convert(varchar(8), crdate, 108) + '\"'\n\u00a0\u00a0 from master..sysobjects\ngo\ndeclare @sql varchar(8000)\nselect @sql = 'bcp \"select * from tempdb..vw_bcpMasterSysobjects\n          order by crdate desc, crtime desc\"\n       queryout c:\\bcp\\sysobjects.txt -c -t, -T -S'\n+ @@servername\nexec master..xp_cmdshell @sql<\/pre>\n<p>Of course, strictly speaking, the view was not necessary at all as the query could have been included in the BCP statement but that can get difficult to maintain.<\/p>\n<p>The <span class=\"CodeInText\">queryout<\/span> method allows you to BCP from the result of a stored procedure, which opens up a lot of possibilities and offers a lot of control over the file format. For anything other than a simple table extract I would tend to use this method rather than a view. I would also format each line within the stored procedure. This means that the formatting can be tested independently from the file creation.<\/p>\n<p>Employing this technique, the above extract becomes:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">use tempdb\ngo\ncreate proc s_bcpMasterSysobjects\nas\n\u00a0\u00a0 select\u00a0\u00a0 '\"' + name + '\"'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + ',' + '\"' + convert(varchar(8), crdate, 112) + '\"'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + ',' + '\"' + convert(varchar(8), crdate, 108) + '\"'\n\u00a0\u00a0 from master..sysobjects\n\u00a0\u00a0 order by crdate desc\ngo\ndeclare @sql varchar(8000)\nselect @sql = 'bcp \"exec tempdb..s_bcpMasterSysobjects\"\n       queryout c:\\bcp\\sysobjects.txt -c -t, -T -S'\n+ @@servername\nexec master..xp_cmdshell @sql<\/pre>\n<h2>More complex formatting<\/h2>\n<p>We can now change the format of the extracted data to anything we require. For example, to include column names at the top and a rowcount at the end:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">use tempdb\ngo\ncreate proc s_bcpMasterSysobjects\nas\n\u00a0\u00a0\u00a0\u00a0\u00a0 set nocount on\n\u00a0\u00a0\u00a0\u00a0\u00a0 \n\u00a0\u00a0\u00a0\u00a0\u00a0 create table #a (seq int, crdate datetime, s varchar(1000))\n\u00a0\u00a0\u00a0\u00a0\u00a0 -- header - column headers\n\u00a0\u00a0\u00a0\u00a0\u00a0 insert\u00a0\u00a0\u00a0\u00a0\u00a0 #a (seq, crdate, s)\n\u00a0\u00a0\u00a0\u00a0\u00a0 select\u00a0\u00a0\u00a0\u00a0\u00a0 1, null,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 '\"name\",\"crdate\",\"crtime\"'\n\u00a0\u00a0\u00a0\u00a0\u00a0 \n\u00a0\u00a0\u00a0\u00a0\u00a0 -- data\n\u00a0\u00a0\u00a0\u00a0\u00a0 insert\u00a0\u00a0\u00a0\u00a0\u00a0 #a (seq, crdate, s)\n\u00a0\u00a0\u00a0\u00a0\u00a0 select\u00a0\u00a0\u00a0\u00a0\u00a0 2, crdate,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'\"' + name + '\"'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + ',' + '\"' + convert(varchar(8), crdate, 112) + '\"'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + ',' + '\"' + convert(varchar(8), crdate, 108) + '\"'\n\u00a0\u00a0\u00a0\u00a0\u00a0 from master..sysobjects\n\n\u00a0\u00a0\u00a0\u00a0\u00a0 -- trailer - rowcount\n\u00a0\u00a0\u00a0\u00a0\u00a0 insert\u00a0\u00a0\u00a0\u00a0\u00a0 #a (seq, crdate, s)\n\u00a0\u00a0\u00a0\u00a0\u00a0 select\u00a0\u00a0\u00a0\u00a0\u00a0 3, null,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'rowcount = ' + convert(varchar(20),count(*)-1)\n\u00a0\u00a0\u00a0\u00a0\u00a0 from #a\n\n\u00a0\u00a0\u00a0\u00a0\u00a0 select\u00a0\u00a0\u00a0\u00a0\u00a0 s\n\u00a0\u00a0\u00a0\u00a0\u00a0 from\u00a0 #a\n\u00a0\u00a0\u00a0\u00a0\u00a0 order by seq, crdate desc\ngo<\/pre>\n<p>This stored procedure will format the data as required, but when it is run via the <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/working-with-the-bcp-command-line-utility\/\">BCP command<\/a> it receives an error:<\/p>\n<p>&#8220;Invalid object name &#8216;#a'&#8221;<\/p>\n<p>Using the profiler you can see that when using <b><span class=\"CodeInText\">queryout<\/span><\/b>, the query is called twice. BCP tries to get the result set format by calling the stored procedure with <span class=\"CodeInText\">fmtonly<\/span>. This does not execute the\u00a0stored procedure\u00a0but returns the format of the resultset. Unfortunately, it does not work if the stored procedure creates and uses a temp table. This problem can be circumvented by including a <span class=\"CodeInText\">set<\/span> <span class=\"CodeInText\">fmtonly<\/span> <span class=\"CodeInText\">off<\/span> command in the execution:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">declare @sql varchar(8000)\nselect @sql = 'bcp \"set fmtonly off exec tempdb..s_bcpMasterSysobjects\"\n       queryout c:\\bcp\\sysobjects.txt -c -T -S' + @@servername\nexec master..xp_cmdshell @sql<\/pre>\n<p>Note that the column terminator is not used since the resultset is a single column.<\/p>\n<p>&#8212;<em><strong>Editor&#8217;s Note<\/strong><\/em>&#8212;<br \/>This was the only piece of code that I failed to get working. The stored procedure executed fine outside the BCP, but when I tried to run the above command I received an error:<\/p>\n<p>SQLState = HY010, NativeError = 0<br \/>Error = [Microsoft][SQL Native Client]Function sequence error<br \/>NULL<\/p>\n<p>The author was unable to replicate this error. Is anyone aware of what might be causing this on my machine?<br \/>&#8212;<em><strong>End Editor&#8217;s Note<\/strong><\/em>&#8212;<\/p>\n<p>Look at this execution using the profiler and you will see <span class=\"CodeInText\">set<\/span> <span class=\"CodeInText\">fmtonly<\/span> <span class=\"CodeInText\">off<\/span> followed by <span class=\"CodeInText\">set<\/span> <span class=\"CodeInText\">fmtonly<\/span> <span class=\"CodeInText\">on<\/span>. There is a potential problem with this though: it means that the\u00a0stored procedure\u00a0is executed twice, once for the format and once to extract the data &#8211; due to the <span class=\"CodeInText\">fmtonly<\/span> setting, both these calls will produce a resultset. This needs to be taken into account when considering how long the process will take and its impact on your system.<\/p>\n<p>As the\u00a0stored procedure\u00a0is executed twice it should not contain any updates of permanent data. Particularly if data is flagged as exported then the flagging will be performed on the first call and no data will be extracted for the second call. All in all, this method should be used with caution and comments (warnings) placed in the\u00a0stored procedure\u00a0for the aid of future maintenance.<\/p>\n<p>To get round all of these problems, use the stored procedure to insert into a table then, from the BCP, extract from that table. This gives you added features that can be valuable on systems that are performing a lot of extracts:<\/p>\n<ul>\n<li>It gives a record of the data extracted &#8211; if the file is lost it can be recreated.<\/li>\n<li>The data extracted can be presented to a user for viewing from the database.<\/li>\n<li>If there are problems with the file, the support can see the data without needing access to the file.<\/li>\n<\/ul>\n<p>For this process, the format stored procedure, <span class=\"CodeInText\">s_bcpMasterSysobjects<\/span>, will insert into the table using an identifier and the BCP will extract using the same identifier. This process can be controlled be (a control) stored procedure which would allocate the identifier and pass it to the format stored procedure which inserted the extracted data into a table with that identifier. It then calls another stored procedure or in-line code to create files (via BCP) for each identifier which is not marked as complete. The identifier is flagged as complete after the file creation. In this way the file creation becomes independent of the extract of the data.<\/p>\n<p>This identifier can be passed to both processes from a controlling stored procedure or the format stored procedure can get the next available identifier and the extract can flag the identifier as extracted &#8211; so it extracts everything that has not been extracted, allowing the export to be independent of the extract.<\/p>\n<p>Which technique you use will depend on your system. The following code demonstrates the use of a controlling stored procedure:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">use tempdb\ngo\ncreate table Extract\n\u00a0\u00a0\u00a0\u00a0\u00a0 (\n\u00a0\u00a0\u00a0\u00a0\u00a0 Extract_ID\u00a0 int ,\n\u00a0\u00a0\u00a0\u00a0\u00a0 Seq1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 varchar(100) null ,\n\u00a0\u00a0\u00a0\u00a0\u00a0 Seq2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 varchar(100) null ,\n\u00a0\u00a0\u00a0\u00a0\u00a0 Seq3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 varchar(100) null ,\n\u00a0\u00a0\u00a0\u00a0\u00a0 Data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 varchar(8000) ,\n\u00a0\u00a0\u00a0\u00a0\u00a0 InsertDate\u00a0 datetime default getdate()\n\u00a0\u00a0\u00a0\u00a0\u00a0 )\ngo\ncreate proc s_bcpMasterSysobjects\n@ExtractID int\nas\ndeclare @rowcount int\n\u00a0\u00a0\u00a0\u00a0\u00a0 set nocount on\n\u00a0\u00a0\u00a0\u00a0\u00a0 \n\u00a0\u00a0\u00a0\u00a0\u00a0 -- header - column headers\n\u00a0\u00a0\u00a0\u00a0\u00a0 insert\u00a0\u00a0\u00a0\u00a0\u00a0 Extract (Extract_ID, Seq1, Data)\n\u00a0\u00a0\u00a0\u00a0\u00a0 select\u00a0\u00a0\u00a0\u00a0\u00a0 @ExtractID,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 '01' ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 '\"name\",\"crdate\",\"crtime\"'\n\u00a0\u00a0\u00a0\u00a0\u00a0 \n\u00a0\u00a0\u00a0\u00a0\u00a0 -- data\n\u00a0\u00a0\u00a0\u00a0\u00a0 insert\u00a0\u00a0\u00a0\u00a0\u00a0 Extract (Extract_ID, Seq1, Seq2, Data)\n\u00a0\u00a0\u00a0\u00a0\u00a0 select\u00a0\u00a0\u00a0\u00a0\u00a0 @ExtractID,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 '02' ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + convert(varchar(100), '99990101' - crdate, 121) ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'\"' + name + '\"'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + ',' + '\"' + convert(varchar(8), crdate, 112) + '\"'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + ',' + '\"' + convert(varchar(8), crdate, 108) + '\"'\n\u00a0\u00a0\u00a0\u00a0\u00a0 from master..sysobjects\n\n\u00a0\u00a0\u00a0\u00a0\u00a0 select @rowcount = @@rowcount\n\u00a0\u00a0\u00a0\u00a0\u00a0 \n\u00a0\u00a0\u00a0\u00a0\u00a0 -- trailer - rowcount\n\u00a0\u00a0\u00a0\u00a0\u00a0 insert\u00a0\u00a0\u00a0\u00a0\u00a0 Extract (Extract_ID, Seq1, Data)\n\u00a0\u00a0\u00a0\u00a0\u00a0 select\u00a0\u00a0\u00a0\u00a0\u00a0 @ExtractID,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 '03' ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'rowcount = ' + convert(varchar(20),@rowcount)\ngo\ncreate proc s_Extract\n@ExtractID int\nas\n\u00a0\u00a0\u00a0\u00a0\u00a0 select\u00a0\u00a0\u00a0\u00a0\u00a0 Data\n\u00a0\u00a0\u00a0\u00a0\u00a0 from\u00a0 Extract\n\u00a0\u00a0\u00a0\u00a0\u00a0 where Extract_ID = @ExtractID\n\u00a0\u00a0\u00a0\u00a0\u00a0 order by Seq1, Seq2, Seq3\ngo<\/pre>\n<p>Now the data is extracted via:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Create table ExportLog (Export_id int, Status varchar(20))\n\nInsert ExportLog select 25, 'Extracting'\nexec tempdb..s_bcpMasterSysobjects 25\nupdate ExportLog set Status = 'Exporting' where Export_id = 25\ndeclare @sql varchar(8000)\nselect @sql = 'bcp \"exec tempdb..s_Extract 25\"\n       queryout c:\\bcp\\sysobjects.txt -c -T -S' + @@servername\nexec master..xp_cmdshell @sql\nupdate ExportLog set Status = 'complete' where Export_id = 25<\/pre>\n<p>You can view the data extracted via:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">exec tempdb..s_Extract 25<\/pre>\n<p>Alternatively, you can use the following, which will also give the date that the data was extracted:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">select * from tempdb..Extract order by Seq1, Seq2, Seq3<\/pre>\n<p><b>Note:<br \/><\/b><i>For the extract I have given three sort columns Seq1, Seq2, Seq3 which are all order ascending. This means that the extracting stored procedure needs to place the values in ascending order (see the date manipulation). This could have ascending and descending sort columns or the use could be dependent on the extract type.<\/i><\/p>\n<p>For a system which is performing a lot of small exports I will usually implement this process, and a similar process for imports.<\/p>\n<h2>Extracting all tables from a database<\/h2>\n<p>This is usually performed to transfer data between servers\/databases and so should use the native data format. The easiest way is to create the BCP statements from a query then execute them:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">select 'exec master..xp_cmdshell' \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + ' '''\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + 'bcp'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + ' ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + ' out'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + ' c:\\bcp\\'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.bcp' \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + ' -N'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + ' -T'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + ' -S' + @@servername\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + ''''\nfrom INFORMATION_SCHEMA.TABLES\nwhere TABLE_TYPE = 'BASE TABLE'<\/pre>\n<p>The result of this will be a series of BCP statements of the form:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">exec master..xp_cmdshell 'bcp tempdb.dbo.Extract out\n     c:\\bcp\\tempdb.dbo.Extract.bcp -N -T -S&lt;servername&gt;'<\/pre>\n<p>which will extract all tables in the database. To import just change the &#8220;out&#8221; to &#8220;in&#8221;.<\/p>\n<h2>Summary<\/h2>\n<p>We have seen how to use BCP to perform a simple extract of a table to a CSV file, how to perform slightly more complex extracts using a view, extracting using a stored procedure to format the data and a system which allows the data to be extracted and saved in a table and viewed\/exported from there. You can also find a guide on PowerShell parameters <a href=\"https:\/\/www.red-gate.com\/simple-talk\/sysadmin\/powershell\/how-to-use-parameters-in-powershell\/\">here.<\/a><\/p>\n<\/div>\n\n\n<section id=\"my-first-block-block_c209c7cc9e183f34af8f85cc7d0bf4a5\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Fast, reliable and consistent SQL Server development&#8230;<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            &#8230;with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                                            <a href=\"https:\/\/www.red-gate.com\/products\/sql-toolbelt-essentials\/\" class=\"btn btn--secondary btn--lg\" aria-label=\"Learn more &amp; try for free: Fast, reliable and consistent SQL Server development...\">Learn more &amp; try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>FAQs: How to create CSV files from SQL Server data using BCP and stored procedures<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. How do you export SQL Server data to a CSV file using BCP?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Use bcp [database.schema.table] out [filepath] -c -t, -T -S [server] at the command line, or execute the same command from T-SQL using xp_cmdshell. The -c switch outputs character data and -t, sets the field delimiter to comma. For query-based exports, use queryout instead of out with the SQL query in quotes.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. How do you add column headers to a BCP CSV export?<\/h3>\n            <div class=\"faq-answer\">\n                <p>BCP doesn&#8217;t natively include column headers. The workaround is to create the header row separately using a query like SELECT &#8216;Col1,Col2,Col3&#8217; exported to the file first, then append the data rows using BCP with the -a flag, or use xp_cmdshell with a type command to concatenate the header file and data file.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. How do you handle commas in data when exporting to CSV with BCP?<\/h3>\n            <div class=\"faq-answer\">\n                <p>If your data contains commas, use an alternative field delimiter that doesn&#8217;t appear in the data &#8211; common choices are pipe (|) with -t| or caret (^) with -t^. Alternatively, wrap field values in quotes by using a format file that specifies text qualifiers.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"<p>Export SQL Server data to CSV files using BCP and stored procedures. Covers delimiters, column headers, xp_cmdshell integration, and custom field terminators.&hellip;<\/p>\n","protected":false},"author":223267,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4515,4516,4170,4179,4150,5134,4151,4254],"coauthors":[48340],"class_list":["post-184","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-bulk-copy-program","tag-csv-files","tag-database-administration","tag-source-control","tag-sql","tag-sql-prompt","tag-sql-server","tag-stored-procedures"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/184","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/223267"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=184"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/184\/revisions"}],"predecessor-version":[{"id":109329,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/184\/revisions\/109329"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=184"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=184"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=184"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=184"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}