Copying Data from SQL Server to a File

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.

Copying a Database Table to a File

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.

Copying a Database Table to a File in a New Format

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