Home / server / Questions / 10505
Accepted
Matt Hanson
Asked: 2009-05-21 11:06:23 +0800 CST

Copy Access tables to MS SQL

  • 772

I have to copy some tables from an Access 2000 database to a SQL Server 2005 database. What's the easiest way to do this?

sql-server-2005 microsoft-access

6 Answers

  1. The easiest way I can think of is to use the SQL Server Import and Export Wizard.

    Start in SSMS and right-click the destination database and select "Tasks --> Import Data...". Basically you just follow the prompts. Select your Access db as the source, specify table/column mapping options (if needed or desired), etc.

    This assumes that this is a one-time import. If it's to be a recurring process you are likely better off creating an SSIS package and scheduling it via the SQL Agent.

    • 3
  2. Best Answer

    You can set up an ODBC connection to the SQL server and export it from Access to SQL via ODBC.

    • 2

  3. In sql MGM studio you can make linked server which will read data from Access database, after that you simply querying T sql to move data from one table to another

    insert into Sql_Table
Select * from LinkedACCES.dbo.AccesTable
    • 2
    1. Select your table
    2. Choose Export from the File menu (or right-click the table and Export)
    3. in the Export Table screen, choose ODBC Databases() from the file types drop-down
    4. Give your table a new name to store in the db (if you want to)
    5. In the Select Data Source box click the New... button
    6. In the Create New Data Source window click the Machine Data Source tab and run through the wizard to enter your sql server and db info (Select SQL Server in the driver list)

    When that's done your table should appear in your SQL Server db.

    • 1

  5. This question is relatively old, and since then SQL Server Migration Assistant has become the tool of choice for this. See my answer to another question for the link to it.

    • 1

  6. Using the wizard in SS Integration Services from SQL 2005 is the easiest way.

    • 0