BCP code to extract a large amount of data from MS SQL Server I have been using Python to perform a SQL query and retrieve data from the previous date every day since June and load it into a Postgres database which is local. Each date has about 2 million rows and 75 columns or so, so it is fairly large and it takes a while to pull.

Can someone give me an example bcp code which will generate a CSV (comma) file? How much data would I be able to pull at once or can I loop through each calendar date in the SELECT statement? I am hoping this will be a faster way to kind of replicate the tables I am interested in. 

My Python code essentially boils down to SELECT * FROM table_name WHERE report_date = {}. The report date is typically yesterday.

Here is my final working result if anyone else every stumbles upon this. The subprocess.run executes the string which contains the BCP query. In the code below, there are a few variables such as the table_name, database name, and query date since I am looping through different tables and databases on different dates.

path = 'C:/raw/'
filename = '{}_{}.csv'.format(database, select_database(database)[table_int])
query = '"SELECT * FROM {}.dbo.{} WHERE row_date = \'{}\'"'.format(database, select_database(database)[table_int], query_date)
command = 'BCP {} queryout \"{}\" -t, -c -S "server_name" -T'.format(query, os.path.join(path, filename))
table_name = 'raw_{}_{}'.format(select_database(database)[table_int], database)
logger.info(command)
subprocess.run(command)
There are plenty of examples on the internet - search for "bcp comma delimited"  Also, refer to the BCP syntax here.

This should get you started:

bcp &lt;YourDatabase&gt;.&lt;YourSchema&gt;.&lt;YourTable&gt; out C:\OutData.dat -o C:\OutData.log -S . -T -c -a 65535' -t, -b100000



BCP - the command
the fully qualified object you're exporting from Sql Server (this could also be a view)
out - the path and file name of the exported data
-o - the output log information about number of rows extracted
-S - the sql server instance name (default here)
-T - use a trusted connection
-c - export the data in character format
-a - packet size of 65535
-t - your column delimiter (comma in this case)
-b - batch size (for performance)