Post reply

Import multi tables from access 200 to sql 2000

  • August 27, 2006 at 12:19 pm

    #173918

    I have multiple tables in a Microsoft Access database

    How can I import them into my SQL database without using the import and export data wizard?

    I dont want to do the wizard 12 times because of the relations that are created with the different tables

    At the moment I have to use the wizard and use the wizard 12 different times to import the data in the correct order

    I checked into BULK INSERT command but I didnt see anything to deal with access database's

    If I have to I could export all the different tables from access to a csv or text file but there has to be an easier way.

    here is a partial example of the database to be imported

    tblStudent

    firstname

    lastname

    studentid (PK)

    majorid (FK)

     

    tblMajor

    majorid (PK, FK)

    majordesc

    In the example above I would need to import the tblmajor prior to the tblstudent due to the way the keys are done, so the wizard would have to be run twice to do it, once to import the tblmajor first then the tblstudent

     

  • August 30, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • August 30, 2006 at 11:36 am

    #657652

    There's an Import Wizard in SQL 2K. Fire up Enterprise Manager, connect to your server, and right click on your database. Select All Tasks, Import Data. That wizard should let you import all 12 tables in one fell swoop. You'll still have to define your relationships, but that's really no more difficult in SQL 2K than Access 2K.

    --2 pennies

    -SJT-

  • August 31, 2006 at 2:55 am

    #657748

    Agreed. However, if the order in which you import the tables is important, or if you have any other conditions that need tweaking, then you can specify this too. You can save the DTS package you create with the wizard, rather than execute it, after you have created it. Try saving it as a .BAS file. Have a look at it and you'll see that it is pretty straightforward to edit. In your case, it is just going to be a matter of altering the order in which the tables are copied.

    There are plenty of articles and even books on DTS which might help when it gets to the details

  • August 31, 2006 at 2:21 pm

    #657917

    Thanks, editing the DTS did the trick, since I already had the Relations set was the reason for needint to import in a specific order... thanx all

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply