How to Update a SQL Server Table Using Data from a Different Source (DataTable)

asp.net c# datatable sqlbulkcopy sql-server-2005

Question

I possessDataTable it comes from an.xls table.

I want to keep this."DataTable into a SQL Server database table that already exists.

I utilizeSqlBulkCopy to keep rows that contain the values 22-zzz.

The issue is that I also have additional entries that use the SQL Server table exact PK but contain cells with various values instead.

In essence:

Let's suppose that IDataTable My argument goes like this:

Name, number, and id(PK)

005 | abc | 123

006 | lge | 122

I have something like this for my SQL server;

Name, number, and id(PK)

004 | cbs | 345

005 | lks | 122

You can see now that the record 006 can be posted into SQL Server immediately by usingSqlBulkCopy . On the other hand, since the SQL server table already includes rows with the same PK, the record 005 cannot be entered using it.

I then made an effort to manually remove the row. Create an ArrayList by extracting each individual cell, then create a UPDATE Table statement. Due to the sheer number of data I need to process, this solution seems to be impractical.

To do this, I'm seeking for a more effective strategy.

We appreciate any assistance.

Thank's

1
1
2/10/2012 3:18:53 AM

Accepted Answer

Use the code below:

Reading data from a DataTable and preparing it for XML using side-by-side C# code

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

Stored Procedure on the database side (you must change the name of your table):

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
2
11/27/2016 9:01:44 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow