BCP code to extract a large amount of data from MS SQL Server

Question

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)

Answer 1

You can either use a query, as needed to BCP out each month, or BCP out the entire table.

bcp "SELECT * FROM table_name WHERE report_date = {}" queryout <FilePath And Filename> -c -t, -T -S "<servername>"

or

bcp <database>.<schema>.<tablename> out <filepath and filename> -c -t, -T -S "<Servername>"

There are multiple switches available for BCP, in this case the following ones are used:

• -c - uses a character datatype using char as the storage type and tab as the seperator
• -t, - replaces the tab separator with a comma
• -T - uses trusted (Windows) authentication

There are many more switches available (MSDN has a full reference) so you might need to adjust things to get what you need. If all else fails you could create format files to ensure that you get the data in the exact method you need it.

Answer 2

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 <YourDatabase>.<YourSchema>.<YourTable> 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)