How to use the SQL Server Bulk Copy Program to move lots of data to SQL Server from Visual FoxPro

MORE INFORMATION

Step-by-step example

1. Execute from SQL Server:

   sp_dboption Mydatabase,bulkcopy,true

2. 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)  )
   						

3. 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.
4. Follow the prompts, and type \t for the field terminator for each field.
5. When prompted, save the format file with a meaningful name such as employee.fmt.
6. Edit the format file with an MS-DOS editor.
7. 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
   						

8. Change the 5th column for the last row to "\r\n" as the end of file marker.
9. 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""

10. When completed, set the Bulkcopy option back to FALSE using Step 1 with the FALSE parameter.