transfer dbf data to sql by query

Posted on 2006-06-06
Medium Priority
Last Modified: 2012-05-05

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


Question by:lanac222
  • 3
  • 2
LVL 16

Expert Comment

ID: 16843533
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Accepted Solution

csachdeva earned 750 total points
ID: 16843993
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

Author Comment

ID: 16844297
#4 is what I want. Thanks.

Author Comment

ID: 16844309
I mean #3.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question