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?
6 Answers
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.

- 218
- 2
- 5
-
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 00:24
-
@squillman - looks like your SSMS caveat was prescient; works > easy +1 – Matt May 21 '09 at 04:30
You can set up an ODBC connection to the SQL server and export it from Access to SQL via ODBC.

- 5,969
- 3
- 24
- 32
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

- 1,521
- 6
- 25
- 41
- Select your table
- Choose Export from the File menu (or right-click the table and Export)
- in the Export Table screen, choose ODBC Databases() from the file types drop-down
- Give your table a new name to store in the db (if you want to)
- In the Select Data Source box click the New... button
- 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.

- 37,883
- 12
- 92
- 146
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.

- 232
- 1
- 7