如何从其他源(DataTable)更新数据的SQL Server表 [英] How to Update SQL Server Table With Data From Other Source (DataTable)
问题描述
我有一个<。c $ c> DataTable ,它是从.xls表生成的。
我想存储这个 DataTable
到SQL Server数据库中的现有表。
我使用 SqlBulkCopy
存储具有唯一PK 的行。
问题是,我还有其他行具有相同的PK 作为SQL Server表,但包含与SQL Server表相比具有不同值的单元格。
简而言之:
在我的 DataTable
中,我有一行如下:
id PK)|名称|号码
005 | abc | 123
006 | lge | 122
对于我的SQL服务器我有这样的sth:
id(PK)|名称|号码
004 | cbs | 345
005 | lks | 122
现在,您看到 006 可以使用 SqlBulkCopy
。另一方面,由于SQL服务器表包含具有相同PK的行,因此无法插入行 005 。
现在我尝试手动提取行。将每个单个单元格提取到一个ArrayList中,然后再生成一个UPDATE TABLE语句。然而,这种方法似乎是不可行的,因为我有这么多行要处理。
我正在寻找一个更好的方法来实现这一目标。
任何帮助都不胜感激。
谢谢
使用以下代码:
C#从DataTable读取数据并准备XML数据的副代码:
DataTable dt = new DataTable();
StringBuilder sb = new StringBuilder();
sb.Append(< R>); (int i = 0; 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>);
///将XML字符串传递给DB端
///
//sb.ToString(); //这里你从数据表中获取所有数据为xml格式
数据库端存储过程(您将需要更新您的表名):
CREATE PROCEDURE dbo.UpdateData
- 添加存储过程的参数
@data XML
AS
BEGIN
SET NOCOUNT ON;
- 将数据保存到临时表
创建表#tmp_data(id nchar(2),名称varchar(20),数字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不存在
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 TABLE
SET name =#tmp_data.name,数字r =#tmp_data.number
FROM #tmp_data
WHERE#tmp_data.id = TABLE.id
commit tran
if(@@ error< ;> 0)
rollback tran
END
I have a DataTable
which is generated from .xls table.
I would like to store this DataTable
into an existing table in SQL Server database.
I use SqlBulkCopy
to store rows which have unique PK.
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.
In short:
Let's say in my DataTable
I have a row like this:
id(PK) | name | number
005 | abc | 123
006 | lge | 122
For my SQL server I have sth like this;
id(PK) | name | number
004 | cbs | 345
005 | lks | 122
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.
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.
Any help is appreciated.
Thank's
Use the below code:
C# Side code for reading data from DataTable and preparing the XML data:
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
Database side Stored Procedure (you will need to 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
这篇关于如何从其他源(DataTable)更新数据的SQL Server表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!