How to Update SQL Server Table With Data From Other Source (DataTable)

Question

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

Can someone help me to format the table? I hope it doesn't look too confusing. — rofans91
– rofans91, Commented Feb 10, 2012 at 3:21
You say you want a successful implementation, but you don't explain what exactly you want to happen for id of "005". Do you want to overwrite the data in the database table? Or just ignore it from the DataTable? — user596075
– user596075, Commented Feb 10, 2012 at 3:21
Why don't you use XML format and Stored Procedure. The flow is.. you prepare XML data from DataTable, then pass to Stored Procedure. In SP, you can read XML as table. Then you can check which IDs are already in Table or not. Insert new data, update or ignore existing Ids. — Thit Lwin Oo
– Thit Lwin Oo, Commented Feb 10, 2012 at 3:27
What I try so far is generate a dynamic Update Table command. Sth like: update name=sth number =sth where id=sth . But as stated in my question I found this method to be unfeasible, therefore I am looking for a better method. — rofans91
– rofans91, Commented Feb 10, 2012 at 3:28
@Thit interesting suggestion. It's something worthed to try, I guess. If you are quite sure about an answer, just simply shoot it. Cheers & Thanks! — rofans91
– rofans91, Commented Feb 10, 2012 at 3:32

TheLethalCoder · Accepted Answer · 2016-11-27 09:01:44Z

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

Collectives™ on Stack Overflow

How to Update SQL Server Table With Data From Other Source (DataTable)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related