BELOW IS FROM msdn:Summary
This article describes how to use the Bulk Copy Program (BCP), a stand- alone utility that ships with SQL Server. If you need to move large amounts of data from Visual FoxPro to SQL Server, the Upsizing Wizard may be too slow. The Bulk Copy Program utility only transfers the data, it does not create the tables. You can use the Upsizing Wizard to create the tables or create them yourself in ISQL/W. In order to prepare SQL Server to accept the data using the "fast" version of BCP, you must execute the sp_dboption system procedure and set the select into/bulkcopy option to true.
More Information
Step-by-Step Example
Following are sample steps using the BCP utility to transfer data from an ASCII file to SQL Server:
Execute from SQL Server:
sp_dboption Mydatabase,bulkcopy,true
Create a tab-delimited ASCII file from the Visual FoxPro Table. For example, use a low-level routine to output your fields into a text file:
=FPUTS(fhandle, emp_id + CHR(9) + ;
last_name + CHR(9) + ;
first_name + CHR(9) + ;
title + CHR(9) + ;
PADR(DTOC(birth_date)+' '+'12:00AM',16,' ') + CHR(9) + ;
PADR(DTOC(hire_date)+' '+ '12:00AM',16,' ') + CHR(9) + ;
address + CHR(9) + ;
city + CHR(9) + ;
region + CHR(9) + ;
postalcode + CHR(9) + ;
STR(salary,12,2) )
Create a bcp Format file necessary to transfer data. For example, from the bin directory under SQL Server:
bcp mydatabase.dbo.employee out c:\temp\emp.txt /Sservename /Usa /Ppassword
NOTE: This command should be on one line.
Follow the prompts, and type \t for the field terminator for each field.
When prompted, save the format file with a meaningful name such as employee.fmt.
Edit the format file with an MS-DOS editor.
Change the second column for each row to SYBCHAR as in the following example using employee.fmt:
6.0
11
1 SYBCHAR 0 6 "\t" 1 emp_id
2 SYBCHAR 0 20 "\t" 2 last_name
3 SYBCHAR 0 10 "\t" 3 first_name
4 SYBCHAR 0 30 "\t" 4 title
5 SYBCHAR 0 16 "\t" 5 birth_date
6 SYBCHAR 0 16 "\t" 6 hire_date
7 SYBCHAR 0 60 "\t" 7 address
8 SYBCHAR 0 15 "\t" 8 city
9 SYBCHAR 0 15 "\t" 9 region
10 SYBCHAR 0 10 "\t" 10 postalcode
11 SYBCHAR 0 8 "\r\n" 11 salary
Change the 5th column for the last row to "\r\n" as the end of file er.
Transfer the ASCII file into SQL Server using BCP as follows:
bcp mydatabase.dbo.employee in c:\temp\employ.txt /femployee.fmt /Sservernamec /Usa /P""
When completed, set the Bulkcopy option back to FALSE using Step 1 with the FALSE parameter.
This article describes how to use the Bulk Copy Program (BCP), a stand- alone utility that ships with SQL Server. If you need to move large amounts of data from Visual FoxPro to SQL Server, the Upsizing Wizard may be too slow. The Bulk Copy Program utility only transfers the data, it does not create the tables. You can use the Upsizing Wizard to create the tables or create them yourself in ISQL/W. In order to prepare SQL Server to accept the data using the "fast" version of BCP, you must execute the sp_dboption system procedure and set the select into/bulkcopy option to true.
More Information
Step-by-Step Example
Following are sample steps using the BCP utility to transfer data from an ASCII file to SQL Server:
Execute from SQL Server:
sp_dboption Mydatabase,bulkcopy,true
Create a tab-delimited ASCII file from the Visual FoxPro Table. For example, use a low-level routine to output your fields into a text file:
=FPUTS(fhandle, emp_id + CHR(9) + ;
last_name + CHR(9) + ;
first_name + CHR(9) + ;
title + CHR(9) + ;
PADR(DTOC(birth_date)+' '+'12:00AM',16,' ') + CHR(9) + ;
PADR(DTOC(hire_date)+' '+ '12:00AM',16,' ') + CHR(9) + ;
address + CHR(9) + ;
city + CHR(9) + ;
region + CHR(9) + ;
postalcode + CHR(9) + ;
STR(salary,12,2) )
Create a bcp Format file necessary to transfer data. For example, from the bin directory under SQL Server:
bcp mydatabase.dbo.employee out c:\temp\emp.txt /Sservename /Usa /Ppassword
NOTE: This command should be on one line.
Follow the prompts, and type \t for the field terminator for each field.
When prompted, save the format file with a meaningful name such as employee.fmt.
Edit the format file with an MS-DOS editor.
Change the second column for each row to SYBCHAR as in the following example using employee.fmt:
6.0
11
1 SYBCHAR 0 6 "\t" 1 emp_id
2 SYBCHAR 0 20 "\t" 2 last_name
3 SYBCHAR 0 10 "\t" 3 first_name
4 SYBCHAR 0 30 "\t" 4 title
5 SYBCHAR 0 16 "\t" 5 birth_date
6 SYBCHAR 0 16 "\t" 6 hire_date
7 SYBCHAR 0 60 "\t" 7 address
8 SYBCHAR 0 15 "\t" 8 city
9 SYBCHAR 0 15 "\t" 9 region
10 SYBCHAR 0 10 "\t" 10 postalcode
11 SYBCHAR 0 8 "\r\n" 11 salary
Change the 5th column for the last row to "\r\n" as the end of file er.
Transfer the ASCII file into SQL Server using BCP as follows:
bcp mydatabase.dbo.employee in c:\temp\employ.txt /femployee.fmt /Sservernamec /Usa /P""
When completed, set the Bulkcopy option back to FALSE using Step 1 with the FALSE parameter.
解决方案 »
免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货