SQL database and dbf Import and Export

Vfpoledb has been installed in the system,
Run the command separately:
Select * from openrowset ('vfpoledb. 1', 'd: \ '; 'admin'; '', 'select column 1 from Table 1 ')
.

However
Insert
Openrowset ('vfpoledb. 1', 'd: \ '; 'admin'; '', 'select column 1 from Table 1 ')
Select column 1 from table 2

Error prompt:
Server: Message 7301, level 16, status 2, Row 1 not
Obtain the required interface from the ole db Provider 'vfpoledb. 1.

Replace it with the following :--------------------------------------------
Insert into OPENROWSET ('Microsoft. JET. OLEDB.4.0 ', 'dbase III; DATABASE = D: \', 'select BM from [yh. dbf] ')
Select BM from yht
This error occurs:
The 'select BM from [yh. dbf] 'object cannot be processed.
Ole db Provider 'Microsoft. JET. OLEDB.4.0 'indicates that this object does not contain any columns.

Refer:

/************** Import the DBF File ****************/
Select * from openrowset ('msdasql ',
'Driver = Microsoft Visual FoxPro Driver;
SourceDB = e: \ VFP98 \ data;
SourceType = DBF ',
'Select * from customer where country! = "USA" order by country ')
Go

/**************** Export to DBF ***************/
If you want to export data to the generated structure (that is, the existing table) FOXPRO table, you can directly use the following SQL statement
Insert into openrowset ('msdasql ',
'Driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = c :\',
'Select * from [aa. DBF] ')
Select * from table

Note:
SourceDB = c: \ specifies the folder where the foxpro table is located
Aa. DBF specifies the name of The foxpro table.

-- Note: do not include any extra spaces in the driver name.

Insert into openrowset ('msdasql ', 'driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = D: \', 'select column 1 from [Table 2.DBF] ')
Select column 1
From table 2

Import and Export
Http://blog.csdn.net/zjcxc/archive/2003/12/29/20085.aspx
Http://dev.csdn.net/develop/article/78/78132.shtm

---------- DBF import SQL Server table ----------

The following uses SQL2000, VFP6, and above as an example.

Method 1: execute the following statement in the query analyzer (select the corresponding database first)

-- If the SQL table that accepts the imported data already exists
Select * from openrowset ('msdasql ', 'driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = c :\', 'select * from aa. dbf ')

-- You can also import the corresponding column name, for example:
Insert into an existing SQL table name (column name 1, column name 2 ...) select (corresponding to column 1, corresponding to column 2 ...) from openrowset ('msdasql ', 'driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = c: \', 'select * from aa. dbf ')

-- If the SQL table that accepts the imported data does not exist, create

-- Method 1:
Select * into the SQL table name to be generated from OPENROWSET ('Microsoft. JET. OLEDB.4.0 ', 'dbase IV; HDR = NO; IMEX = 2; DATABASE = c: \', 'select * from dbf table name. dbf ')

-- Method 2:
Select * into the SQL table name to be generated from OPENROWSET ('Microsoft. JET. OLEDB.4.0 ', 'dbase III; HDR = NO; IMEX = 2; DATABASE = c: \', 'select * from dbf table name. dbf ')

-- Method 3:
Select * into the SQL table name to be generated from openrowset ('msdasql ', 'driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = c :\', 'select * from dbf table name. dbf ')

-- After the SQL SERVER is imported using the first two methods, if the source table is opened with VFP, the system does not prompt "file cannot be accessed". After the statement is executed, the source table is disabled. However, it is not satisfactory that the SQL table fields in the source table are converted to NVARCHAR after being imported using the first two methods.
-- The third method has one drawback: After importing the DBF table into SQL Server, you can use VISUAL FOXPRO to open the DBF table immediately and the system will prompt "file access unavailable ", that is, the table is still opened by SQL. However, after about one minute, you can open the DBF table, indicating that the analyzer will shut down the table after a period of time.

-- You can export only a few fields.
Select * into the SQL table name to be generated from openrowset ('msdasql ', 'driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = c: \', 'select Field 1, field 2 from aa. dbf ')

/* -- Description:
SourceDB = c: \ is the directory where dbf Files are stored.
Aa. DBF is the name of the dbf file.
--*/

Pass

It is better to use SQL to import and export data directly.

Use SQL SERVER to import and export data directly. Select DBASE5 as the export type. When 0, an error occurs when you press next ??

MARK

---------- DBF import SQL Server table ----------

The following uses SQL2000, VFP6, and above as an example.

Method 1: execute the following statement in the query analyzer (select the corresponding database first)

-- If the SQL table that accepts the imported data already exists
Select * from openrowset ('msdasql ', 'driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = c :\', 'select * from aa. dbf ')

-- You can also import the corresponding column name, for example:
Insert into an existing SQL table name (column name 1, column name 2 ...) select (corresponding to column 1, corresponding to column 2 ...) from openrowset ('msdasql ', 'driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = c: \', 'select * from aa. dbf ')

-- If the SQL table that accepts the imported data does not exist, create

-- Method 1:
Select * into the SQL table name to be generated from openrowset ('msdasql ', 'driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = c :\', 'select * from dbf table name. dbf ')

-- Method 2:
Select * INTO TEMP1 from openrowset ('vfpoledb. 1', 'c: \ '; 'admin'; '', 'select * from dbf table name. dbf ')
If VFP is not installed, copy vfpoledb. dll to the SYSTEM directory and register it.

-- Method 3:
Select * into the SQL table name to be generated from OPENROWSET ('Microsoft. JET. OLEDB.4.0 ', 'dbase IV; HDR = NO; IMEX = 2; DATABASE = c: \', 'select * from dbf table name. dbf ')

-- Method 4:
Select * into the SQL table name to be generated from OPENROWSET ('Microsoft. JET. OLEDB.4.0 ', 'dbase III; HDR = NO; IMEX = 2; DATABASE = c: \', 'select * from dbf table name. dbf ')

-- After the SQL SERVER is imported in the first two ways, the "file cannot be accessed" prompt is not displayed when the source table is opened with VFP. This means that the source table is closed after the statement is executed. However, it is not satisfactory that the SQL table fields in the source table are converted to NVARCHAR after being imported using the first two methods.
-- The third method has one drawback: After importing the DBF table into SQL Server, you can use VISUAL FOXPRO to open the DBF table immediately and the system will prompt "file access unavailable ", that is, the table is still opened by SQL. However, after about one minute, you can open the DBF table, indicating that the analyzer will shut down the table after a period of time.

-- You can export only a few fields.
Select * into the SQL table name to be generated from openrowset ('msdasql ', 'driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = c: \', 'select Field 1, field 2 from aa. dbf ')

/* -- Description:
SourceDB = c: \ is the directory where dbf Files are stored.
Aa. DBF is the name of the dbf file.
--*/

Method 2: use SQL Server's "import and export data" tool (DTS)

1. Create a data source for Visual FoxPro:

Follow these steps to create a Visual FoxPro data source (ODBC-Microsoft Visual FoxPro Driver) in ODBC:
Open the ODBC preparation window->
Select the "user DSN" page or "system DSN" page. The latter is recommended->
Click "add"->
Select the Driver "Microsoft Visual FoxPro Driver"->
Enter the Data Source Name in the Data Source Name field, which can be customized. For example, LjDbf and Database Type (Database Type) select "Free Table directory" (Free Table Path), and set the Path of the DBF Table to be imported in the Path below. This column is shown in "D: \ "->
Click "OK" (OK) to complete preparation->
Close ODBC preparation window

2. Import and export data through SQL (DTS) to export the DBF table into an SQL table:

Start->
Program->
Microsoft SQL Server-data transmission->
Next->
In this step, select the "Select data source" window, and select "Microsoft Visual FoxPro Driver" for "Data Source ", at the same time, select the name of the data source you just prepared in "User/system DSN" (for example, LjDbf, of course, the above data source preparation process can also be completed through the "user/system DSN" followed by a "new" button), and then click "Next"->
This step is the "select target" window. Select "Microsoft ole db provider for SQL Server" for "purpose" (this option is generally the default ), at the same time, select the database, server, and server verification method for the SQL table you want to import, and then click "Next"->
At this time:
A: If you want to import all the fields in the DBF table, select "Copy table and view from source database" and click "Next"->
Select the DBF table to be imported and modify the name of the generated SQL table in the 'target' field. By default, the name of the generated SQL table is the same as that of the DBF table. Click "Next"->
Click "Next"->
Click "finish" to import the data. (If the import is successful or not, DTS will prompt you ).

B: If you only import several or all fields in the DBF table (if you import all fields, you can also use step A above. The result is the same, but the method is different ), select "Use a query item to specify the data to be output" and click "Next"->
Click "Query Builder", select the DBF table to be imported (select a column), and click "Next"->
Specify the sorting order. Select one or more sorting fields. If no sorting is required, click "Next"->
You can specify query conditions in this step. If no conditions exist, click "Next"->
The corresponding SQL statement appears (you can modify the generated SQL statement, but not recommended), and then click "Next"->
You can modify the name of the SQL table to be generated. The default value is "result", and then click "Next"->
Click "Next"->
Click "finish" to import the data. (If the import is successful or not, DTS will prompt you ).

Method 3:

First, convert the DBF table to EXCEL or TXT, and then import the EXCEL or TXT file to the SQL SERVER Import and Export tool (this method is similar to method 2 and will not be introduced ).

---------- SQL Server table export DBF ----------

Method 1: If the DBF table already exists, run the following statement in the 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

/* -- Description:
SourceDB = c: \ is the directory where dbf Files are stored.
Table B. DBF is an existing dbf file name. import data to this table
Table A. dbf is the name of the SQL table to be exported.
--*/

Method 2: If the DBF table does not exist, create it directly when exporting the table and use the "Import and Export data" tool of SQL Server.

1. Create a data source for Visual FoxPro:

Follow these steps to create a Visual FoxPro data source (ODBC-Microsoft Visual FoxPro Driver) in ODBC:
Open the ODBC preparation window->
Select the "user DSN" page or "system DSN" page. The latter is recommended->
Click "add"->
Select the Driver "Microsoft Visual FoxPro Driver"->
Enter the Data Source Name in the Data Source Name field, which can be customized. For example, LjDbf and Database Type (Database Type) select "Free Table directory" (Free Table Path), and then set a Path in the Path below, such as "D: \"->
Click "OK" (OK) to complete preparation->
Close ODBC preparation window

2. Import and export data through SQL (DTS) to export the SQL table into a DBF table:

Start->
Program->
Microsoft SQL Server-data transmission->
Next->
In this step, select the "Select data source" window, and select "Microsoft ole db provider for SQL Server" (This item is generally the default ), select the authentication method for the database, server, and server where the SQL table you want to export resides, and then click "Next"->
This step is the "select target" window. Select "Microsoft Visual FoxPro Driver" for "target ", at the same time, select the name of the data source you just prepared in "User/system DSN" (for example, LjDbf, of course, the above data source preparation process can also be completed through the "user/system DSN" followed by a "new" button), and then click "Next"->
At this time:
A: To export all fields of the exported SQL table, select "Copy table and view from source database" and click "Next"->
Select the table or view to be exported, and modify the name of the generated DBF table in the 'target' field. By default, the name of the generated DBF table is the same as that of the SQL table. Click "Next"->
Click "Next"->
Finally, click "finish" to export the table (DTS will prompt whether the export is successful or not). After the export is successful, the main file name of the DBF table is the same as that of the SQL table.

B: If only several or all fields in the SQL table are exported (if you export all fields, you can use step A above. The result is the same, but the method is different ), select "Use a query item to specify the data to be output" and click "Next"->
Click "Query Builder", select the SQL table to be exported (select a column), and click "Next"->
Specify the sorting order. Select one or more sorting fields. If no sorting is required, click "Next"->
You can specify query conditions in this step. If no conditions exist, click "Next"->
The corresponding SQL statement appears (you can modify the generated SQL statement, but not recommended), and then click "Next"->
You can modify the name of the DBF table to be generated. The default value is "result", and then click "Next"->
Click "Next"->
Click "finish" to export the data. (if the export is successful or not, DTS will prompt you ).

Note:
After the export is successful, the DBF file is generally stored in the Microsoft SQL Server \ 80 \ Tools \ Binn or Program Files \ Common Files \ ODBC \ Data Sources directory, and is not stored in the preceding D: \, I don't know why. If anyone knows this, please let me know.