Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

6 Answers 6

up vote 2 down vote accepted

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

share|improve this answer

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.

share|improve this answer
    
Aye, 'tis a good method (assuming access to SSMS)! Probably easier than my answer +1 –  squillman May 20 '09 at 19:29
    
Alas, no access to SSMS. :( –  Matt Hanson May 21 '09 at 0:24
    
@squillman - looks like your SSMS caveat was prescient; works > easy +1 –  Matt May 21 '09 at 4:30

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
share|improve this answer
  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.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.