SQL Server 2012 Import DBF file

step one. Put the DBF file under any system directory, I put it in the D:\db directory

Step Two: Need to install AccessDatabaseEngine.exe (ACE)

Website:

https://www.microsoft.com/en-us/download/details.aspx?id=13255

After installation, you will see the following interface in the "Linked server", "Provider", under the instance of SQL Server.

Step Three: Execute the SQL query statement in SSMS

Select*from OPENROWSET (' microsoft.ace.oledb.12.0 ', ' dBase IV; hdr=no;imex=2;database=d:/db ', ' select * from [2015.DBF] '

There will be an error, similar

Execute the following statement:

exec sp_configure ' show advanced options ', 1

Reconfigure
exec sp_configure ' Ad Hoc distributed Queries ', 1
Reconfigure

EXEC master.dbo.sp_MSset_oledb_prop n ' microsoft.ace.oledb.12.0 ', n ' allowinprocess ', 1

EXEC master.dbo.sp_MSset_oledb_prop n ' microsoft.ace.oledb.12.0 ', n ' dynamicparameters ', 1

Step Four: Execute the SQL query command again

Select*from OPENROWSET (' microsoft.ace.oledb.12.0 ', ' dBase IV; hdr=no;imex=2;database=d:/db ', ' select * from [2015.DBF] '

Prompt as follows

You need to install FoxPro and enter the following command in the FoxPro command box

Use ' d:\db\2015.dbf ' EXCLUSIVE;

COPY to ' d:\db\2015new.dbf ' TYPE fox2x;

Once again, the new DBF file 2015new.dbf should be selected, and finally the DBF file content is successfully read. Courier is still relatively fast, more than 1 million of the data of the full table for about 1 minutes.

Select*from OPENROWSET (' microsoft.ace.oledb.12.0 ', ' dBase IV; hdr=no;imex=2;database=d:/db ', ' select * from [2015new. DBF] ')

Summary of Knowledge points:

1. The following command functions

exec sp_configure ' show advanced options ', 1

Reconfigure

exec sp_configure ' Ad Hoc distributed Queries ', 1
Reconfigure

EXEC master.dbo.sp_MSset_oledb_prop n ' microsoft.ace.oledb.12.0 ', n ' allowinprocess ', 1

EXEC master.dbo.sp_MSset_oledb_prop n ' microsoft.ace.oledb.12.0 ', n ' dynamicparameters ', 1

2. Why do you want to duplicate a copy of

Use ' d:\db\2015.dbf ' EXCLUSIVE;

COPY to ' d:\db\2015new.dbf ' TYPE fox2x;

SQL Server 2012 Import DBF file