主页 / dba / 问题 / 120661
Accepted
JohnG
Asked: 2015-11-11 12:36:10 +0800 CST

在 SQL Server 中，是否还有其他数据导入和导出选项

  • 772

我从 SQL Server 7 开始就一直在使用 SQL Server。所以我了解 BCP、批量插入、openrowset 和 SSIS。

我的问题很简单：

  • 您是否知道使用 SQL server Express 版本 SQL 2005 至 2014（以及即将推出的 2016）导入/导出数据的任何其他方法。

我想确保没有任何其他问题，因为使用 BCP（这是我现在正在使用的）需要使用xp_cmdshell并且出于安全原因，需要一个xp_cmdshell_proxy帐户。我在工作中受到挑战，想确保在我回应之前不存在任何其他问题，或者这是 Microsoft SQL Server (Express) 中的新问题。

sql-server import

1 个回答

  1. Best Answer

    SQLCMD 可用于导出数据。默认输出有时会很笨拙，但您可以使用一些命令行选项来关闭标题、修剪尾随空格并使用逗号分隔符而不是默认空格：

    sqlcmd -Q "SET NOCOUNT ON; SELECT TOP(10) name, object_id, type FROM sys.objects;" ^
  -h-1 -W -s ","

    退货：

    sysrscols,3,S
sysrowsets,5,S
sysclones,6,S
sysallocunits,7,S
sysfiles1,8,S
sysseobjvalues,9,S
syspriorities,17,S
sysdbfrag,18,S
sysfgfrag,19,S
sysdbfiles,20,S

    使用-o选项将输出发送到文件：

    sqlcmd -Q "SET NOCOUNT ON; SELECT TOP(10) name, object_id, type FROM sys.objects;" ^
  -h-1 -W -s "," -o C:\temp\SysObjects.txt

    可以创建自定义 .NET 程序（控制台应用程序、Windows 应用程序，甚至 Web 应用程序）来导入和/或导出。对于导入，由于表值参数 (TVP) 在这里不是一个选项，因此有时将数据转换/序列化为 XML 更快，然后执行将接受 XML 参数并使用该.nodes()函数的存储过程，将其反序列化回来到可以通过INSERT INTO table (fields) SELECT x.value(),... FROM @XmlParam.nodes();构造插入的行中。当然，您不想以这种方式处理 100 万行，因此您会将导入文件分成 X 行的批次并一次发送 100 - 500 行。如果有大量数据，那么直接语句可能会更好，但在这种情况下，您可能希望使用SqlTransactionINSERT将每批 X 行包装到一个显式事务中。

    根据您（或您的同事）对 .NET 编程的熟悉程度，您可以使用 SQLCLR 构建您自己的自定义导入和/或导出功能。这适用于从 SQL Server 2005 开始的所有版本的 SQL Server。

    按照这些思路，有一个商业上可用的 SQLCLR 函数和过程库，称为SQL#（我是它的创建者）。一些函数和存储过程处理导入和导出数据。File_SplitIntoFields存储过程返回一个结果集，其中每一行都是来自输入文件的一行，但文件中的每个字段都是一个单独的列。默认情况下，每个字段的数据类型是NVARCHAR(MAX)，但有一个可选的输入参数来指定每个列的确切数据类型。这使得它很容易做到：

    INSERT INTO dbo.MyImportTable (Col1, Col2, Col3, Col4)
  EXEC SQL#.File_SplitIntoFields
    @FilePath = N'',
    @RegExDelimiter = N',',
    @DataTypes = N'NVARCHAR(100),INT,DATETIME,BIT';

    还有一个名为INET_SplitIntoFields的等效存储过程执行相同的操作，但它不是从文件中获取数据，而是直接从 URL 流式传输数据（因此无需先在本地下载文件）。

    对于导出，有一个名为DB_BulkExport的存储过程，它结合了 SQLCMD 和 SSIS 的许多功能。我在需要进行多次导出并且无法在 SQLCMD 和 SSIS 之间做出决定之后创建了这个，因为每个都有其优点和缺点，并且每个都有一个或两个另一个没有的功能，但所有这些功能都是需要。例如，DB_BulkExport不需要任何硬编码的输出字段规范，可以处理SELECT *查询（我不知道如何在 SSIS 中管理，但使用 SQLCMD 很容易）。它还可以处理字段的文本限定，而无需更新源查询以连接其中的双引号（这使得查询非常丑陋且难以维护）。事实上，它可以设置为文本限定所有字段，或者只是根据数据类型（字符串、日期、GUID 等）需要它的字段。

    • 4

相关问题