transfer dbf data to sql by query


Anybody know how to tranfer data from dbf file to sql table by query, NOT DTS tool? Or tranfer in c# code?


Who is Participating?
There are many ways to get the data from a dbf file.
For example:
1. You can define a linked server and connect to the dbf file, and then query the data from that file.
2. You can use DTS, or SSIS in SQL 2K5 to get the data.
3. The simplest, most dynamic, but also the one that is most difficult to tune (to my opinion) is by simply using the OPENROWSET command, which created a connection to the dbf file opn the fly and terminated the connection upon completion.

I like method #3 the most and will only elaborate on this one: To use the OPENROWSET command, simply follow the template provided below.

   'Driver={Microsoft dBase Driver (*.dbf)};DBQ=D:\MyFolderNameWhereDBFFileIsLocated\',
   'Select * from DBFTableName')

This command can be run from within SQL server (e.g., Server Management Studio, Query Analyzer, etc.) and will retrieve all the table records. Then, you can handle or manipulate the returned data as you wish.

What I would recommend is the following:

1. After you perform the SELECT command from the dbf source, write the contents of the dbf table into a temporary table (either a table variable or a temp table, depending on the size of the dbf table).

If you use temp tables in #1 you can also make sure that the removal of data entries from your database table and the population of new records from the dbf file are all transacted.

2. Either remove all entries from the underlying table and insert all records from the temp table containing the data from the dbf file, or alternatively, insert records from the dbf table that are missing from the underlying table and remove (delete) unneeded entries from the original table.

Again, you can wrap these operations in a transactions to ensure that the population of the new data is successful.

If you need any help with performing the above, please let us know, however these actions should be straightforward. Please keep in mind that you can easily write the results of the OPENROWSET command into a temp table or table variable by using a single INSERT INTO or SELECT INTO commands (the latter only works with temp tables).

Please let us know whether this answers your question. There are many other ways to get the data as well, so if this solution does not suffice, we can explore other solutions.

Chetan Sachdeva
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

lanac222Author Commented:
#4 is what I want. Thanks.
lanac222Author Commented:
I mean #3.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.