transfer dbf data to sql by query

Posted on 2006-06-06
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
    LVL 16

    Expert Comment

    LVL 16

    Expert Comment

    LVL 16

    Expert Comment

    LVL 4

    Accepted Solution

    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

    #4 is what I want. Thanks.

    Author Comment

    I mean #3.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
    I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now