如何使用单个查询将数据集中的多条记录插入 SQL Server 2005？

发布于 2024-09-26 14:38:15 字数 70 浏览 1 评论 0 原文

我在 ADO.NET 中有一个数据集，其中包含来自用户端的多个记录。我需要将单个查询中的所有这些行插入数据库中，以避免多个查询

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

相守太难 2024-10-03 14:38:15

也许像批量复制这样的东西就是一个答案。下面代码项目中的示例展示了如何使用数据表来执行此操作，但您应该能够更改示例以使用数据集。

下面是一小部分代码，涵盖了 SQL Server 中的连接和执行（取自 CodeProject ）。

需要注意的关键部分是bulkcopy.WriteToServer(SourceTable); SourceTable 是否是您要传递给它的 DataSet 的一部分

//First create a connection string to destination database
string connectionString;
connectionString = <EM>YourConnectionString</EM>and
    Initial Catalog=TestSMODatabase";

//Open a connection with destination database;
using (SqlConnection connection = 
       new SqlConnection(connectionString))
{
   connection.Open();

   //Open bulkcopy connection.
   using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
   {
    //Set destination table name
    //to table previously created.
    bulkcopy.DestinationTableName = "dbo.TestTable";

    try
    {
       bulkcopy.WriteToServer(SourceTable); // SourceTable would come from your DataSet
    }
    catch (Exception ex)
    {
       Console.WriteLine(ex.Message);
    }

    connection.Close();
   }
}

Maybe something like a Bulk copy would be an answer. The example in Code Project below show how to do it using a DataTable, but you should be able to change the example around to use a DataSet.

Below is an small part of the code which covers to conenction and exection in SQL Server (taken from CodeProject).

The key part to notice is the bulkcopy.WriteToServer(SourceTable); were the SourceTable would be the part of the DataSet you would pass to it

//First create a connection string to destination database
string connectionString;
connectionString = <EM>YourConnectionString</EM>and
    Initial Catalog=TestSMODatabase";

//Open a connection with destination database;
using (SqlConnection connection = 
       new SqlConnection(connectionString))
{
   connection.Open();

   //Open bulkcopy connection.
   using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
   {
    //Set destination table name
    //to table previously created.
    bulkcopy.DestinationTableName = "dbo.TestTable";

    try
    {
       bulkcopy.WriteToServer(SourceTable); // SourceTable would come from your DataSet
    }
    catch (Exception ex)
    {
       Console.WriteLine(ex.Message);
    }

    connection.Close();
   }
}

回复收藏 0 原文

醉梦枕江山 2024-10-03 14:38:15

虽然 SqlBulkCopy 适用于批量插入，但您无法执行批量更新。这对您来说可能是问题，也可能不是问题，但在任何情况下，您都可以使用利用 OPENXML 以允许批量插入和更新。您还需要 sp_xml_preparedocument 和 sp_xml_removedocument。

此方法的另一个巨大优点是，您可以使用 OUTPUT 子句。

批量插入示例。

假设您已在定义要插入或更新的数据的存储过程上定义了参数@p_XmlData VARCHAR(MAX)。对于需要传入的 XML 示例，您可以执行以下操作：

SELECT TOP 1 *, 0 AS __ORDERBY FROM dbo.YourEntity AS YourEntity FOR XML AUTO, ROOT('ROOT')

然后存储过程将如下所示

DECLARE @hDoc INT
EXEC sp_xml_preparedocument @hDoc OUTPUT, @p_XmlData

INSERT INTO dbo.YourEntity
(
        [Field1],
        [Field2]
)
SELECT
        XMLData.Field1,
        XMLData.Field2
FROM OPENXML (@hdoc, 'ROOT/YourEntity', 1)
WITH
(
        [Field1] int,
        [Field2] varchar(50),
        [__ORDERBY] int
) AS XMLData

EXEC sp_xml_removedocument @hDoc

While SqlBulkCopy works for bulk inserts, you can't do bulk updates. This may or may not be an issue for you, but in any case you can use a stored procedure which utilises OPENXML to allow for bulk inserts and updates. You'll also need sp_xml_preparedocument and sp_xml_removedocument.

Another great advantage of this method is that you can get the ids of the entities you just bulk inserted, using the OUTPUT clause.

Example Of A Bulk Insert.

Assuming you have defined a parameter @p_XmlData VARCHAR(MAX) on the stored proc defining the data to insert or update. For an example of the XML you need to pass in, you can do:

SELECT TOP 1 *, 0 AS __ORDERBY FROM dbo.YourEntity AS YourEntity FOR XML AUTO, ROOT('ROOT')

Then the stored procedure will look something like this

DECLARE @hDoc INT
EXEC sp_xml_preparedocument @hDoc OUTPUT, @p_XmlData

INSERT INTO dbo.YourEntity
(
        [Field1],
        [Field2]
)
SELECT
        XMLData.Field1,
        XMLData.Field2
FROM OPENXML (@hdoc, 'ROOT/YourEntity', 1)
WITH
(
        [Field1] int,
        [Field2] varchar(50),
        [__ORDERBY] int
) AS XMLData

EXEC sp_xml_removedocument @hDoc

回复收藏 0 原文