ruhk has asked for the wisdom of the Perl Monks concerning the following question:

I have a project for work which requires me, among other things, to migrate a 30,000 record table from an Access Database to a Microsoft SQL server.

I am not overly experianced at perl or database programing, and the first thing that I came up with was to just do a select * from the access database and copy it over.

Is there a simpler way by chance? Like a copy table that could accomplish the same thing easier? Thanks

Replies are listed 'Best First'.
Re: Migrating Table in a Database
by dragonchild (Archbishop) on Jan 06, 2004 at 16:25 UTC
    As they're both MS products, you might want to look at migration tools that MS provides. If you dig into the SQL*Server docs, I'll bet there's a tool that converts Access to SQL*Server. (I cannot imagine there isn't one cause SQL*Server licenses cost more than Access licenses ...)

    As for Perl methods ... I would add more than just a INSERT INTO foo SELECT * FROM bar. I'd look at the table and see if it can't be normalized into a few more tables. Access doesn't allow for very good normalization, so that's the first thing I look at when dealing with it.

    ------
    We are the carpenters and bricklayers of the Information Age.

    Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

[reply]
[d/l]
Re: Migrating Table in a Database
by MADuran (Beadle) on Jan 06, 2004 at 19:30 UTC
[reply]
Re: Migrating Table in a Database
by hmerrill (Friar) on Jan 06, 2004 at 20:34 UTC

    Although I haven't worked with either Access or MS SQL server, this should be a trivial exercise for DBI. In your perl script, you would use DBI to open up a database connection to each of the two databases. Then setup a fetch loop that fetches Access rows, and for each Access row fetched, insert a new row into the MS SQL Server database.

    The hardest part will be getting the connection parameters right - I'm guessing you'll need DBD::ODBC to access the Access database, and I'm not sure what DBD you'll need for the MS SQL Server database - maybe DBD::ODBC will work for that as well.

    Of course, take this with a grain of salt again since I have no experience with MS Access or MS SQL Server. But using Perl with DBI and DBD::your database to connect to multiple database at the same time really should be a pretty trivial exercise.

    HTH.

[reply]
Re: Migrating Table in a Database
by chimni (Pilgrim) on Jan 07, 2004 at 08:28 UTC
    HI,
    If you have perl installed on you windows box ,then i suggest you use DBI.
    This node will give you an idea of migration (flat file to sqlite)http://www.perlmonks.com/index.pl?node_id=317830
    You can apply the same solution in your scenario
    I suggest you look up O'Reilly's "Programming the perl DBI" by Tim bunce and Al Descartes.
    HTH
[reply]
Re: Migrating Table in a Database
by ruhk (Scribe) on Jan 07, 2004 at 14:04 UTC
    Thanks - The data is now sitting nice and happy in the SQL database. :P
[reply]
      Ok, but how did you end up doing it?
[reply]

Back to Seekers of Perl Wisdom