How to Extract Data from a Database Using a Command Prompt
Issue
Exporting data from a table in database.
Product Line
BCP (Bulk Copy Program)
Environment
Command prompt window
Cause
Sometimes the customer may not be tech-savvy and it is easier to extract the required information by passing a command and asking them to run it from a DOS window.
Resolution
BCP is a utility that can be executed from the command line and has the following syntax:
Here is an example of how to extract data from 'Source' table in ION_Data:
C:\>BCP "select * from ion_data.dbo.source" queryout C:\Temp\SourceNames.txt -SCB9RBM313\ION -w -T
Where:
–S: specifies the instance name to connect to
-w: performs copy operation using Unicode characters
–T: designates a trusted connection and BCP uses the Windows credentials; In case there is no Windows credentials, –U (specifies the login name) and –P (specifies the password) can be used instead.
queryout: copies from a query and must be specified only when bulk copying data from a query
A text file is created in the specified destination.
Here is how the data is formatted in the text file:
Exporting data from a table in database.
Product Line
BCP (Bulk Copy Program)
Environment
Command prompt window
Cause
Sometimes the customer may not be tech-savvy and it is easier to extract the required information by passing a command and asking them to run it from a DOS window.
Resolution
BCP is a utility that can be executed from the command line and has the following syntax:
bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
[-mmax_errors] [-fformat_file] [-x] [-eerr_file]
[-Ffirst_row] [-Llast_row] [-bbatch_size]
[-n] [-c] [-w] [-N] [-V (60 | 65 | 70 | 80)] [-6]
[-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term]
[-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
[-Sserver_name[\instance_name]] [-Ulogin_id] [-Ppassword]
[-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]
{in | out | queryout | format} data_file
[-mmax_errors] [-fformat_file] [-x] [-eerr_file]
[-Ffirst_row] [-Llast_row] [-bbatch_size]
[-n] [-c] [-w] [-N] [-V (60 | 65 | 70 | 80)] [-6]
[-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term]
[-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
[-Sserver_name[\instance_name]] [-Ulogin_id] [-Ppassword]
[-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]
Here is an example of how to extract data from 'Source' table in ION_Data:
C:\>BCP "select * from ion_data.dbo.source" queryout C:\Temp\SourceNames.txt -SCB9RBM313\ION -w -T
Where:
–S: specifies the instance name to connect to
-w: performs copy operation using Unicode characters
–T: designates a trusted connection and BCP uses the Windows credentials; In case there is no Windows credentials, –U (specifies the login name) and –P (specifies the password) can be used instead.
queryout: copies from a query and must be specified only when bulk copying data from a query
A text file is created in the specified destination.
Here is how the data is formatted in the text file:
Released for: Schneider Electric USA
Need help?
Product Selector
Quickly and easily find the right products and accessories for your applications.
Get a Quote
Start your sales inquiry online and an expert will connect with you.
Where to buy?
Easily find the nearest Schneider Electric distributor in your location.
Help Center
Find support resources for all your needs, in one place.