You can copy tables out of SQL Server for use with other programs, such as a spreadsheet program, or for use on other SQL Servers. The following examples show how to copy a database table to a file in its current format, as well as how to copy a database table to a file in a new format.
In the following example, bcp copies data from the publishers table to an output file called PUBL_OUT for later reloading into SQL Server using the format file PUBL_FMT. It creates an output file with a new line at the end of each row and a comma between each field in a row.
bcp pubs..publishers out publ_out /Sservername /Usa /Ppassword
Enter the file storage type of field pub_id [char]:
Enter prefix length of field pub_id [0]:
Enter length of field pub_id [4]:
Enter field terminator [none]: ,
Enter the file storage type of field pub_name [char]:
Enter prefix length of field pub_name [0]:
Enter length of field pub_name [40]:
Enter field terminator [none]: ,
Enter the file storage type of field city [char]:
Enter prefix length of field city [0]:
Enter length of field city [20]:
Enter field terminator [none]: ,
Enter the file storage type of field state [char]:
Enter prefix length of field state [0]:
Enter length of field state [2]:
Enter field terminator [none]: ,
Enter the file storage type of field country [char]:
Enter prefix length of field country [0]:
Enter length of field country [30]:
Enter field terminator [none]: \n
Do you want to save this format information in a file? [Y/n] y
Host filename: [bcp_fmt] publ_fmt
Starting copy...
8 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total = 150 Avg = 18 (53.33 rows per sec.)
These are the results in the PUBL_OUT output file:
0736,New Moon Books,Boston,MA,USA 0877,Binnet & Hardley,Washington,DC,USA 1389,Algodata Infosystems,Berkeley,CA,USA 1622,Five Lakes Publishing,Chicago,IL,USA 1756,Ramona Publishers,Dallas,TX,USA 9901,GGG&G,München,,Germany 9952,Scootney Books,New York,NY,USA 9999,Lucerne Publishing,Paris,,France
In this example, the user saved a format file called PUBL_FMT in response to the associated prompt. These are the results in the PUBL_FMT file:
6.0 5 1 SQLCHAR 0 4 "," 1 pub_id 2 SQLCHAR 0 40 "," 2 pub_name 3 SQLCHAR 0 20 "," 3 city 4 SQLCHAR 0 2 "," 4 state 5 SQLCHAR 0 30 "\r\n" 5 country
To use the saved format file to copy this data back into SQL Server, type:
bcp pubs..publishers in publ_out /f publ_fmt /Sservername /Usa /Ppassword
Note that the PUBL_FMT format file can be used to copy any data with the same format into SQL Server.
By changing the default values of the bcp prompts, you can store data in a new format for use with other software. In most cases, you'll want an ASCII file for other programs. The usual responses to the bcp prompts are as follows:
The following example creates output in the computer format called system data format (SDF). Each field has a fixed length with spaces to pad the fields. Adjacent fields in which the data completely fills the first field seem to run together, since there are no field separators on each line of output. Only the final field has a terminator, the newline character(\n). This format can be easily read or produced by other software.
bcp pubs..sales out sal_out /Sserver /Usa /Ppassword
Enter the file storage type of field stor_id [char]:
Enter prefix-length of field stor_id [0]:
Enter length of field stor_id [4]:
Enter field terminator [none]:
Enter the file storage type of field ord_num [char]:
Enter prefix-length of field ord_num [0]:
Enter length of field ord_num [20]:
Enter field terminator [none]:
Enter the file storage type of field date [datetime]: char
Enter prefix-length of field date [0]:
Enter length of field date [26]:
Enter field terminator [none]:
Enter the file storage type of field qty [smallint]: char
Enter prefix-length of field qty [0]:
Enter length of field qty [6]:
Enter field terminator [none]:
Enter the file storage type of field payterms [char]:
Enter prefix-length of field payterms [0]:
Enter length of field payterms [12]:
Enter field terminator [none]:
Enter the file storage type of field title_id [char]:
Enter prefix-length of field title_id [0]:
Enter length of field title_id [6]:
Enter field terminator [none]: \n
Do you want to save this format information in a file? [Y/n] y
Host filename: [bcp_fmt] sal_fmt
Starting copy...
21 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total = 210 Avg = 10 (100.00 rows per sec.)
The following output is sent by bcp to the SAL_OUT file:
6380 |
6871 |
Sep 14 1994 12:00AM |
5 |
Net 60 |
BU1032 |
6380 |
722a |
Sep 13 1994 12:00AM |
3 |
Net 60 |
PS2091 |
7066 |
A2976 |
May 24 1993 12:00AM |
50 |
Net 30 |
PC8888 |
7066 |
QA7442.3 |
Sep 13 1994 12:00AM |
75 |
ON invoice |
PS2091 |
7067 |
D4482 |
Sep 14 1994 12:00AM |
10 |
Net 60 |
PS2091 |
7067 |
P2121 |
Jun 15 1992 12:00AM |
40 |
Net 30 |
TC3218 |
7067 |
P2121 |
Jun 15 1992 12:00AM |
20 |
Net 30 |
TC4203 |
7067 |
P2121 |
Jun 15 1992 12:00AM |
20 |
Net 30 |
TC7777 |
7131 |
N914008 |
Sep 14 1994 12:00AM |
20 |
Net 30 |
PS2091 |
7131 |
N914014 |
Sep 14 1994 12:00AM |
25 |
Net 30 |
MC3021 |
7131 |
P3087a |
May 29 1993 12:00AM |
20 |
Net 60 |
PS1372 |
7131 |
P3087a |
May 29 1993 12:00AM |
25 |
Net 60 |
PS2106 |
7131 |
P3087a |
May 29 1993 12:00AM |
15 |
Net 60 |
PS3333 |
7131 |
P3087a |
May 29 1993 12:00AM |
25 |
Net 60 |
PS7777 |
7896 |
QQ2299 |
Oct 28 1993 12:00AM |
15 |
Net 60 |
BU7832 |
7896 |
TQ456 |
Dec 12 1993 12:00AM |
10 |
Net 60 |
MC2222 |
7896 |
X999 |
Feb 21 1993 12:00AM |
35 |
ON invoice |
BU2075 |
8042 |
423LL922 |
Sep 14 1994 12:00AM |
15 |
ON invoice |
MC3021 |
8042 |
423LL930 |
Sep 14 1994 12:00AM |
10 |
ON invoice |
BU1032 |
8042 |
P723 |
Mar 11 1993 12:00AM |
25 |
Net 30 |
BU1111 |
8042 |
QA879.1 |
May 22 1993 12:00AM |
30 |
Net 30 |
PC1035 |