最近，我不得不将数据从Access 数据库^{(Access database)}迁移到SQL Server 2014  ，因为我的数据库变得太大，Access无法处理。尽管过程相当简单，但我想我会写一篇带有分步说明的文章。

首先，您需要确保您的计算机上安装了SQL Server或SQL Server Express 。在个人计算机上下载SQL Server Express时，请确保下载带有Advanced Services的版本。如果不这样做，则不会安装数据库引擎^{(database engine)}，并且您将拥有没有任何数据库实例^{(database instance)}可连接的SQL Management Studio 。

正确安装SQL Server后，您可以继续执行以下步骤，将Access 数据库导入^{(Access database)}SQL Server中的新数据库。

将 Access 数据库传输^{(Transfer Access Database)}到SQL Server

打开SQL Server Management Studio 并连接^{(SQL Server Management Studio and connect)}到要将Access 数据库导入的^{(Access database)}数据库服务器^{(database server)}。在Databases下，右键单击并选择New Database。如果您已经有一个数据库并且您只想从Access导入几个表，那么只需跳过此步骤并转到下面的导入数据^{(Import Data)}步骤。只需右键单击^{(Just right-click)}您当前的数据库，而不是创建一个新的。

如果您正在创建一个新数据库，请继续为其命名并配置^{(name and configure)}设置，如果您想更改它们的默认值。

现在我们需要右键单击我们刚刚创建的Test 数据库并选择^{(Test database)}Tasks然后Import Data。

在 选择数据源^{( Choose a Data Source)}对话框中，从下拉框中选择 Microsoft Access（Microsoft Jet 数据库引擎） ^{(Microsoft Access (Microsoft Jet Database Engine) )}。

在文件名^{(File nam)}旁边，单击浏览^(Browse)并导航到要导入的Access 数据库，然后单击^{(Access database)}打开^(Open)。请注意，数据库不能是 Access 2007 或更高格式 ( ACCDB )，因为SQL Server无法识别它！因此，如果您有 2007 到 2016 年的Access 数据库^{(Access database)}，首先通过转到 文件 - 另存为将其转换为^{( File – Save As)}2002-2003 年数据库^{(2002-2003 Database)} 格式 ( MDB ) 。

继续并单击下一步^(Next)以选择目的地。由于您右键单击要将数据导入到的数据库，因此应该已经在列表中选择了它。如果没有，请从目标^{(Destination)}下拉列表中选择SQL Native Client 。您应该在服务器名称^{(Server Name)}下看到数据库实例^{(database instance)}，然后在选择身份验证方法后能够在底部选择特定数据库。

单击下一步^(Next)，然后通过选择从一个或多个表中复制数据^{(Copy the data from one or more tables)}或编写查询以指定^{(Write a query to specify the data to transfer)}要传输的数据，指定您希望如何将数据从Access传输到SQL。

如果要从Access 数据库^{(Access database)}中复制所有表或仅复制部分表而不进行任何数据操作^{(data manipulation)}，请选择第一个选项。如果您只需要从表中复制某些行和列数据，则选择第二个选项并编写SQL 查询^{(SQL query)}。

默认情况下，应选择所有表，如果单击Edit  Mappings按钮，您可以配置字段在两个表之间的映射方式。如果您为导入创建了一个新数据库，那么它将是一个精确的副本。

在这里，我的Access 数据库^{(Access database)}中只有一个表。单击“下一步”^{(Click Next)}，您将看到“运行包”^{( Run Package)}屏幕，其中应检查“立即运行” 。^{( Run Immediately)}

单击下一步^(Next)，然后单击完成^(Finish)。然后，您将看到数据传输的进度。完成后，您将在Message列中看到为每个表传输的行数。

单击关闭^(Close) ，您就完成了。您现在可以继续在您的表上运行SELECT以确保所有数据都已导入。现在您可以享受SQL Server管理数据库的强大功能了。

将数据从Access^(Access)导入SQL Server有任何问题吗？如果是这样，请发表评论，我会尽力提供帮助。享受！

Migrate Data from MS Access to SQL Server Database

Recently, І had to migrate the data from an Access database to SQL Server 2014 becauѕe my database was getting too large for Access to hаndle. Even though the process is fairly simple, I figured I would write an article with step-by-step instructions.

First, you need to make sure you have SQL Server or SQL Server Express installed on your computer. When downloading SQL Server Express on a personal computer, make sure you download the version with Advanced Services. If you don’t, then the database engine won’t be installed and you’ll have SQL Management Studio without any database instance to connect to.

Once you have SQL Server installed properly, you can continue with the steps below to import the Access database into a new database in SQL Server.

Transfer Access Database to SQL Server

Open SQL Server Management Studio and connect to the database server you want to import your Access database into. Under Databases, right-click and choose New Database. If you already have a database and you simply want to import a couple of tables from Access, then just skip this and go to the Import Data step below. Just right-click on your current database instead of creating a new one.

If you’re creating a new database, go ahead and give it a name and configure the settings if you want to change them from the defaults.

Now we need to right-click on the Test database we just created and choose Tasks and then Import Data.

On the Choose a Data Source dialog box, select Microsoft Access (Microsoft Jet Database Engine) from the drop down box.

Next to File name, click on Browse and navigate to the Access database you want to import and click Open. Note that the database cannot be in Access 2007 or higher format (ACCDB) as SQL Server does not recognize it! So if you have a 2007 to 2016 Access database, first convert it to the 2002-2003 Database format (MDB) by going to File – Save As.

Go ahead and click Next to choose the destination. Since you right-clicked on the database you wanted to import the data into, it should already be picked in the list. If not, select SQL Native Client from the Destination drop down. You should see the database instance under Server Name and then be able to choose the specific database at the bottom once you pick your method of authentication.

Click Next and then specify how you want to transfer the data from Access to SQL by choosing either Copy the data from one or more tables or Write a query to specify the data to transfer.

If you want to copy all the tables or just some of the tables from the Access database without any data manipulation, choose the first option. If you need to copy only certain rows and columns of data from a table, then choose the second option and write a SQL query.

By default, all the tables should be selected and if you click the Edit Mappings button, you can configure how the fields map between the two tables. If you created a new database for the import, then it’ll be an exact copy.

Here I have only one table in my Access database. Click Next and you’ll see the Run Package screen where Run Immediately should be checked.

Click Next and then click Finish. You’ll then see the progress of the data transfer as it occurs. After it is completed, you’ll see the number of rows transferred for each table in the Message column.

Click Close and you’re done. You can now go ahead and run a SELECT on your tables to make sure all the data got imported. Now you can enjoy the power of SQL Server for managing your database.

Any problems importing your data from Access to SQL Server? If so, post a comment and I’ll try to help. Enjoy!

Related posts

• 绘制 Excel 数据图表

• 如何在 Excel 中过滤数据

• 使用 Excel Mobile 新的“从图片插入数据”功能

• 使用汇总函数汇总 Excel 中的数据

• 使用 Excel 作为从 Web 复制数据的工具

• Microsoft Outlook 没有响应？8个修复尝试

• 如何在Microsoft Word Document中插入签名

• 如何为电子邮件和任务创建 Microsoft Outlook 快捷方式

• 12 个有用的 Microsoft Word 提示和技巧

• 如何安全Password Protect Excel File

• Ultimate Guide修复损坏或Corrupt Word File

• 如何在Outlook中创建Distribution List

• 什么是Microsoft MyAnalytics以及如何使用它？

• 13 OneNote Tips & Tricks更好地组织笔记

• 如何在Word and Excel中创建Flowchart

• 将线性Regression Trendline添加到Excel Scatter Plot

• 如何在Microsoft Word中Create and Use AutoText

• 如何免费获得 Microsoft Word

• 如何在Word中制作Booklet

• 如何在Microsoft Excel中创建Gantt Charts