如何更新SQL Server表使用Data从其他来源（数据表） [英] How to Update SQL Server Table With Data From Other Source (DataTable)

问题描述

我有一个数据表这是从.xls的表生成。

I have a DataTable which is generated from .xls table.

我想存储该数据表到SQL Server数据库的现有表。

I would like to store this DataTable into an existing table in SQL Server database.

我用 SqlBulkCopy的存储行具有独特的PK

问题是，我也有有同PK 为SQL Server表，但包含其他行的细胞不同的值相比，SQL Server表。

Problem is, I also have other rows which have same PK as SQL Server table but contain cells with different value compared to SQL Server table.

在短：

让在我的 DataTable中说我有一排这样的：

Let's say in my DataTable I have a row like this:

ID（PK）|名称|号

id(PK) | name | number

005 | ABC | 123

005 | abc | 123

006 | LGE | 122

006 | lge | 122

有关我的SQL Server我有这样的事物;

For my SQL server I have sth like this;

ID（PK）|名称|号

id(PK) | name | number

004 | CBS | 345

004 | cbs | 345

005 | LKS | 122

005 | lks | 122

现在你看行的 006 可以马上使用上传到SQL Server SqlBulkCopy的。在另一方面该行的 005 不能使用它，因为SQL Server表中包含具有相同的PK行插入。

Now you see the row 006 can be uploaded straight away into SQL Server using SqlBulkCopy. On the other hand the row 005 can't be inserted using it since SQL server table contains row with identical PK.

现在我试图手动提取该行。提取每个单格成一个ArrayList然后再把生成一个UPDATE TABLE语句。然而，这种方法似乎是不可行的，因为我有这么多行来处理。

Now I tried to manually extract the row. Extract each single cell into an ArrayList then generate an UPDATE Table statement afterwards. However this method seems to be unfeasible as I have so many rows to process.

我要寻找一个更好的方法来实现这一目标。

I am looking for a better method to achieve this goal.

任何帮助是AP preciated。

Any help is appreciated.

感谢的

推荐答案

普莱舍try..any问题，让我知道。

Plese try..any issue, let me know.

-------- C＃code ------------（读数从数据表ñprepare XML数据的数据）

--------C# code------------(reading data from DataTable n prepare XML data)

DataTable的DT =新的DataTable（）;
        StringBuilder的SB =新的StringBuilder（）;

DataTable dt = new DataTable(); StringBuilder sb = new StringBuilder();

    sb.Append("<R>");
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        sb.Append("<C><ID>" + dt.Rows[0].ToString() + "</ID>");
        sb.Append("<N>" + dt.Rows[1].ToString() + "</N>");
        sb.Append("<I>" + dt.Rows[2].ToString() + "</I></C>");
    }

    sb.Append("</R>");

    ///pass XML string to DB side
    ///
    //sb.ToString(); //here u get all data from data table as xml format

------- DB侧存储过程[更新表格名称] --------------

-------DB side Stored Proc [update your table name]--------------

CREATE PROCEDURE dbo.UpdateData 
    -- Add the parameters for the stored procedure here
    @data       XML
AS
BEGIN
    SET NOCOUNT ON;

    -- keep data into temp table
    create table #tmp_data (id nchar(2),name varchar(20), number int)

    DECLARE @XMLDocPointer INT  
    EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @DATA

    INSERT INTO #tmp_data(id,name,number)
    SELECT  ID,N,I
    FROM OPENXML(@XMLDocPointer,'/R/C',2)
    WITH(
            ID  nchar(30),
            N   VARCHAR(20),
            I   int
        )

    EXEC sp_xml_removedocument @XMLDocPointer

    begin tran
        -------------------INSERT not existing ones
        INSERT INTO TABLE (id,name,number)
        SELECT id,name,number
        FROM #tmp_data
        WHERE NOT EXISTS
        (
            SELECT 1
            FROM TABLE
            WHERE ID = #tmp_data.ID
        )

        --- update existing ones
        UPDATE  TABLE
        SET name = #tmp_data.name, number = #tmp_data.number
        FROM #tmp_data
        WHERE #tmp_data.id = TABLE.id

        commit tran

    if(@@error <> 0)
        rollback tran

END

这篇关于如何更新SQL Server表使用Data从其他来源（数据表）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！