[Reprint] In SQL Server, how do I implement an import or export between a DBF file and a SQL Server table?

Originally using SQL Server 2000 databases, the DTS tool makes it easy to import and export data between SQL Server and DBF files, and now, after installing SQL Server2005, discovers the SQL Server Import Export Wizard that it provides The data sources in the database are not rich in the original, and the DBF file support is missing.

1. Right-click the database and choose Tasks > Import data to open the SQL Server Import and Export Wizard dialog box.
2. In data source, select Microsoft OLE DB Provider for Visual FoxPro, and click the Properties button to open the Data Connection Properties dialog box.
3. Click the "..." button after select or enter a database name to select one of the databases or free tables and click the OK button to close the Data Connection Properties dialog box.
4. Click Next to make the Select Destination page and click Next directly.
5. On the Specify table copy or query page, click Next directly.
6. After selecting the Visual FoxPro table to import on the Select Source Tables and Views page, click Next.
7, then "Next" until completed.

It is important to note that the data type of your table is to be successfully converted from SQL Server, or an error occurs.

VFP MVP

Go download it here http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en

Also available in the VFP9 installation CD

====================================== other methods-not tested ========================================================

DBF Import SQL Server

--If the SQL table that accepts imported data already exists

Insert into already exists SQL table name Select * FROM OPENROWSET (' Msdasql ', ' Driver=microsoft Visual FoxPro Driver; SOURCETYPE=DBF; Sourcedb=c: ', ' select * from AA. DBF ')

--You can also import the corresponding column names, such as:

Insert into an already existing SQL table name (column name 1, column Name 2 ...) Select (corresponding column name 1, corresponding column Name 2 ...) From OPENROWSET (' Msdasql ', ' Driver=microsoft Visual FoxPro Driver; SOURCETYPE=DBF; Sourcedb=c: ', ' select * from AA. DBF ')


-------------if the SQL table that accepts imported data does not exist, the import creates


One drawback: After importing the DBF table into SQL Server, open the DBF table with Visual FoxPro immediately and prompt "Cannot access the file", that is, the table is opened by SQL. But after 1 minutes or so, and then open the DBF table, it shows that after a period of time the Query Analyzer to close the table.

Select * into the SQL table name to generate from OPENROWSET (' Msdasql ', ' Driver=microsoft Visual FoxPro Driver; SOURCETYPE=DBF; Sourcedb=c: ', ' select * from DBF table name. DBF ')

-Method Two: There is a drawback: after the DBF table is imported into SQL Server, open the DBF table with Visual FoxPro immediately, will prompt "Cannot access the file", that is, the table is opened by SQL. But after 1 minutes or so, and then open the DBF table, it shows that after a period of time the Query Analyzer to close the table.

Select * into TEMP1 from OPENROWSET (' vfpoledb.1 ', ' C: '; ') Admin '; ', ' select * ' from DBF table name. DBF ')
-If you do not have VFP installed, you need to copy the Vfpoledb.dll to the system directory and register it.


--Method Three: After importing SQL Server, the source table is opened with VFP without prompting "Cannot access file", stating that the source table is closed after execution of the statement. However, there are also undesirable places, that is, the first two ways to import, the source table after the Character Field import SQL table fields corresponding to become nvarchar.

Select * into the SQL table name to generate from OPENROWSET (' MICROSOFT. JET. oledb.4.0 ', ' dBase IV; Hdr=no;imex=2;database=c: ', ' select * from DBF table name. dbf ')

--Method Four: After importing SQL Server, the source table is opened with VFP without prompting "Cannot access file", stating that the source table is closed after execution of the statement. However, there are also undesirable places, that is, the first two ways to import, the source table after the Character Field import SQL table fields corresponding to become nvarchar.

Select * into the SQL table name to generate from OPENROWSET (' MICROSOFT. JET. oledb.4.0 ', ' dBase III; Hdr=no;imex=2;database=c: ', ' select * from DBF table name. dbf ')

--Method Five: When using the BDE driver to import, and then open the source table with VFP will not prompt "Cannot access files"

Select * from OPENROWSET (' msdasql ', ' collatingsequence=ascii;dbq=d:send;defaultdir=d:send;deleted=0;driver={driver Do Microsoft dBase (*.dbf)};fil=dbase 5.0 ', ' SELECT * from DBF table name. DBF ') A
--Premise: First install the BDE driver on the machine


--You can also guide only a few fields

Select * into the SQL table name to generate from OPENROWSET (' Msdasql ', ' Driver=microsoft Visual FoxPro Driver; SOURCETYPE=DBF; Sourcedb=c: ', ' Select field 1, Field 2 from AA. DBF ')

/* Description:
SOURCEDB=C:C: is the storage directory for DBF files
Cal DBF is the DBF file name
*/

Tool Import 1: Through the SQL Server Import Export Data tool (DTS)

1. Create a Visual FoxPro data source:

The steps to establish a Visual FoxPro data source (odbc-microsoft Visual FoxPro Driver) in ODBC are as follows:
Open the ODBC Configuration window,
Select the User DSN page or the System DSN page, which is recommended
Click the "Add" button
Select the "Microsoft Visual FoxPro Driver" Driver
Enter this data source name at data source name (stream name), which can be customized, such as Ljdbf,database type (database type) at the Select "Free Table directory", Then set the path to the DBF table you want to import at the path below, such as "D:"
Click "OK" to complete the configuration.
Close the ODBC Configuration window

2. Using SQL Import and Export data (DTS) to lead the DBF table into SQL table:

Start
Programs,
Microsoft Sql server-Import Export Number (DTS)
Next
This step is the Select Data Source window, data source selects Microsoft Visual FoxPro Driver, and at the User/System DSN Select the data source name you just configured (this example LJDBF, of course, the process of preparing the data source above can also be done through the "user/ System DSN "is followed by a new button to complete), then click Next
This step is the "Select Purpose" window, "for the purpose" option "Microsoft OLE DB Provider for SQL Server" (this is typically the default), select the SQL table you want to host the database, server and server authentication method, and then click "Next"
Then:
A: If you want to import all the DBF table fields, select Copy tables and views from the source database, click Next.
Select the DBF table to import, the name of the generated SQL table can be modified at ' destination ', by default, as with the DBF table name, click Next
Then click Next.
Finally click "Finish" to import it (DTS will be prompted if the import succeeds or not).

B: If you import only a few or all of the fields in the DBF table (if you import all the fields, you can use step a above, the result is the same, but the method is different), select "Specify the data to output with a single query", click "Next"
Click "Query Builder", select the DBF table to import (at this point you can select columns), click "Next"
Specify the sort order, select one or more sort fields, and if you don't want to sort, this step is a little bit, click Next.
This step can specify the query criteria, if there is no condition, this step can be slightly, click "Next",
A corresponding SQL statement appears (you can also modify the generated SQL statement, but it is not recommended), and then click Next.
You can modify the SQL table name you want to generate by default to "results" and then click "Next"
Then click Next.
Finally click "Finish" to import it (DTS will be prompted if the import succeeds or not).



Tool Import 2:

Turn the DBF table into Excel or TXT first, and then import the SQL Server Import and Export tool into Excel or TXT (this method is no longer described in method two).

----------The SQL Server table to export DBF----------

Method One: If the DBF table already exists, execute the following statement in Query Analyzer (select the corresponding database first)


Insert into OPENROWSET (' msdasql ', ' Driver=microsoft Visual FoxPro Driver; SOURCETYPE=DBF; Sourcedb=c: ', ' select * from Table b.dbf ') SELECT * FROM Table A

/* Description:
SOURCEDB=C:C: is the storage directory for DBF files
Table b.dbf is a DBF file name already exists, data is imported into this table
Table A.DBF is the name of the SQL table to export
*/


Method Two: If the DBF table does not exist, it is created directly when exported, through the SQL Server Import Export Data tool

1. Create a Visual FoxPro data source:

The steps to establish a Visual FoxPro data source (odbc-microsoft Visual FoxPro Driver) in ODBC are as follows:
Open the ODBC Configuration window,
Select the User DSN page or the System DSN page, which is recommended
Click the "Add" button
Select the "Microsoft Visual FoxPro Driver" Driver
Enter this data source name at data source name (stream name), which can be customized, such as Ljdbf,database type (database type) at the Select "Free Table directory", Then set a path at any of the following path, such as "D:"
Click "OK" to complete the configuration.
Close the ODBC Configuration window


2, through the SQL import and Export data (DTS) to the SQL table into the DBF table:

Start
Programs,
Microsoft Sql server-Import Export Number (DTS)
Next
This step is the "choose a data source" window, "Data source" selected "Microsoft OLE DB Provider for SQL Server" (this is typically the default), and select the database, server, and server for which you want to export the SQL table, and then click Next.
This step is the "Select Purpose" window, "purpose" selects "Microsoft Visual FoxPro Driver", and at the User/System DSN Select the data source name you just configured (this example LJDBF, of course, the process of preparing the data source above can also be done through the "User/System DSN "There is a new button to complete), then click" Next "
Then:
A: If you want to export all the fields of an exported SQL table, select Copy tables and views from the source database, click Next.
Select the table or view that you want to export, modify the name of the generated DBF table at ' Destination ', and, by default, the SQL table name, click Next.
Then click Next.
Finally click "Finish" to export (DTS will be prompted if the export succeeds), the main file name of the DBF table after the successful export is the same as the SQL table name.

B: If you export only a few fields or all of the fields in the SQL table (if you export all fields, you can use step a above, the result is the same, but the method is different), select "Specify the data to output with a single query", click "Next"
Click "Query Builder", select the SQL table to export (you can select the column at this time), click "Next"
Specify the sort order, select one or more sort fields, and if you don't want to sort, this step is a little bit, click Next.
This step can specify the query criteria, if there is no condition, this step can be slightly, click "Next",
A corresponding SQL statement appears (you can also modify the generated SQL statement, but it is not recommended), and then click Next.
You can then modify the name of the DBF table to be generated, default to "Results", then click "Next"
Then click Next.
Finally click "Finish" To export it (DTS will be prompted to export success or not).

Note:
After the export is successful, the DBF file is generally stored in the Microsoft Sql server80toolsbinn or program FilesCommon filesodbcdata sources directory, and does not exist in the above mentioned D: I do not know why, Any one who knows, please tell them.

Oconnection = CreateObject ("Adodb.connection")
With oconnection
. ConnectionString = ' Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:; Extended Properties=dbase 5.0; Persist Security Info=false '
. Open
Endwith
TEXT to Lcstr NoShow
Select * into the DBF table name to generate from SQL table name in [ODBC] [Odbc;driver=sql Server; Uid=sa; pwd=; server= computer name; database= Database name]
Endtext
Oconnection.execute (LCSTR)
Oconnection.close

Note: The SQL 2008 default installation does not have the FoxPro data adapter: Http://www.microsoft.com/downloads/en/details.aspx? familyid=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&displaylang=en

[Reprint] In SQL Server, how do I implement an import or export between a DBF file and a SQL Server table?