Database Reference
In-Depth Information
In the above general format, an item enclosed by a pair of square brackets is optional. he
keyword
query
is an SQL statement that generates the export result set. he
{in | out | format |
queryout}
expression gives data transfer directions. he vertical bar represents the OR operator.
he word
format
indicates that a format ile is created. he word
queryout
is used to export the
result set returned by an SQL statement or stored procedure. Using the word
-parameter
, you can
specify the data format or other options. he commonly used parameters are
[
-m
max_errors
] [
-f
format_ile
] [
-e
err_ile
]
[
-F
irst_row
] [
-L
last_row
] [
-b
batch_size
]
[
-n
native data type
] [
-c
char data type
] [
-w
unicode data type
]
[
-N
native export for nonchar data or unicode export for char data
]
[
-6
use 6.x data types
] [
-q
use quoted identiier
]
[
-C
code_page
] [
-t
ield_terminator
] [
-r
row_terminator
]
[
-i
input_ile
] [
-o
output_ile
] [
-a
packet_size
]
[
-S
server_name
[
\
instance_name
]] [
-U
login_id
] [
-P
password
]
[
-T
use trusted connection to SQL Server
] [
-v
bcp version
]
[
-k
preserve null values
] [
-E
preserve id values
] [
-h “
hint
[
,
...
n
]
”
]
he following activity will demonstrate how to use the bcp for data transfer.
ACTIVITY 5.5 EXPORTING DATA FROM WINDOWS
AZURE SQL DATABASE USING BCP
To see how the bcp utility exports and imports data, let us consider an example that will
export the data in the table EMPLOYEE to a text ile and then import the data from the text
ile to another table called EMPLOYEE_IMPORT. he following command will be used to
export data out of your Windows Azure SQL Database.
bcp Computer_Store.dbo.EMPLOYEE out Employee.txt -c -U COSC6336@
vvzr5w29lp -S tcp:vvzr5w29lp.database.windows.net -P password
In our example, since the current user is the table owner, the middle part of the three-
part table name is missing. hat is, the table name is
Computer_Store..EMPLOYEE
. In
this example, no query is used. he result set contains the entire table. his example uses
the
out
keyword to specify that the data in the table
EMPLOYEE
will be exported to the
Employee.txt
ile. he use of
-c
indicates that the data are exported as characters.
-U
is
used to specify the user at Windows Azure SQL Database and
-P
speciies the password.
-S
speciies the connection string for the remote Windows Azure SQL Database. You can
follow the steps below to run the command in the Windows command prompt.
1. Assume that you are connected to the virtual machine in Windows Azure. To run the
command, press the
Windows Icon
key. Right click the
Developer Command
icon
on the tile and click
Run as administrator
on the ribbon.
2. Enter the above bcp command and press the
Enter
key. Figure 5.36 shows the export
command execution result.
