如何更新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屋!