使用 TSQL 脚本将数据从 Excel 或 Access 导入 SQL Server

引言

在使用了 The Code Project 网站多年来寻求解决方案的帮助后，我觉得是时候开始自己做贡献了。 这是我的第一篇文章，请多多包涵。

背景

是否曾需要从 Access 将数据导入 SQL Server，而这需要比 SSIS 向导允许的更多调整？ 是否想过是否可以使用 T-SQL 脚本完成？

作为一名专业开发人员，我经常需要在“上线”之前将数据从旧系统（Excel 和 Access）导入到 SQL Server。 通常，这可以使用 SQL Server SSIS 向导完成。 在最近一次，我发现自己需要将数据从一个单表 Access 系统导入到 38 个关系型 SQL 表。 原始数据一团糟，在导入之前需要大量清理和转换。

由于有超过一百万行数据（大部分存储为 Access 文本字段），我需要一个能以最简单方式自动化流程的解决方案。 考虑到这一点，我开始研究 SQL 脚本，通过它们我可以操作数据（例如，在首先将国家/地区名称插入到国家/地区表中作为一个简单的例子之后，用查找代码替换国家/地区名称），从而创建一个完全关系化的模型，其中包含提供的源数据。

Using the Code

此处显示的示例可以从 SQL Server Management Studio 或 O-SQL 命令中使用。

在使用该示例之前，您必须在 SQL Server 2005 中允许 AdHoc 远程查询。为此，请从 SQL Server 2005 安装目录启动 Surface Area Configuration 工具，然后选择“功能”的 Surface Area Configuration。

选择您希望在其上执行导入的服务器，然后在组件列表中选择标题为“Ad Hoc Remote Queries”的条目。 在页面右侧，确保选中“启用 OPENROWSET 和 OPENDATASOURCE”的复选框。 应用您的更改并关闭该工具。

现在是有趣的部分！

我使用了一个临时表来导入数据，因为它是一次性导入，但没有任何阻止您使用永久表完成此任务的方法。

然后，第一个任务是创建您的表（此示例使用临时表，因此在表名前面有 #。 要创建永久表，只需指定表名，不要使用开头的 #）

CREATE TABLE [dbo].[#tblImport](
    [Field1] [int] not null,
    [Field2] [varchar] (255) null,
    [Field3] [varchar] (255) null,
    [Field4] [datetime] null)

现在我们需要从外部源获取数据。

我使用的源是一个 Access 数据库，它有“非法”字段名，例如空格、& 符号等。我将数据导入 SQL Server 的唯一方法是明确指定源和目标字段。

要打开 Access 数据源，您可以使用 SQL 命令 OPENDATASOURCE 如下

OPENDATASOURCE(provider_name,init_string)

因此，假设 Access 数据源名为My Database.mdb，并且位于C: 驱动器的根目录下，则 OPENDATASOURCE 语句如下所示

OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','C:\My Database.mdb')

现在我们需要导入数据。 在我的例子中，我只有一个表需要导入，但必须将其转换为多个 SQL 表。 获得源数据后，您可以使用标准的 T-SQL 语句来完成您需要做的任何事情。

要将数据导入到上面创建的临时表中，请使用以下内容

INSERT INTO [dbo].[#tblImport]
    Field1,Field2,Field3,Field4
SELECT [Field1],[Field2],[Field3],[Field4] FROM OPENDATASOURCE_
    ('Microsoft.Jet.OLEDB.4.0','C:\My Database.mdb')...[TableName]

请注意该语句末尾的 ...[TableName]。 三个点是必不可少的，TableName 是您要从中导入的源表的名称。

上述导入阶段完成后，根据需要转换数据以导入到您的 SQL 表中。

如果像我一样，您使用临时表进行初始导入，请不要忘记在完成后删除它！！

 DROP TABLE [dbo].[#tblImport]

结论

使用 T-SQL 导入数据是控制最终结果的一种非常强大的方法，如果您精通 SQL，您可以使用导入的数据进行转换的可能性是无限的。