How to bulk insert using StoreProcedure in SQL Server?

 Described in shortly................................

create a dataset named = dsName  which contain a table name tblName with field(ID,NAME,ADDRESS) in c#

// in c#

public SaveData(DataSet rqdXmlv = null)
{
----------------------------------
----------------------------------
SqlCommand cmd;
cmd = new SqlCommand(procedure, dbConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@dsxmlu", SqlDbType.Xml).Value = (rqdXmlv == null ? null : rqdXmlv.GetXml());
-----------------------------------
------------------------------------
}

//in sql procedure

CREATE PROCEDURE [SP_TEST]
    @dsxmlu xml = null,



/------GET DATA FROM PARAM
        SELECT
            t.value('ID[1]','nvarchar(12)')AS CHNUM,
            t.value('NAME[1]','nvarchar(100)')AS ITMNAME,
            t.value('ADDRESS[1]','nvarchar(100)')AS UNIT
        into #tbl1a FROM
        @dsxmlu.nodes('/dsName/tblName')AS TempTable(t)


  INSERT INTO TEST(ID,NAME,ADDRESS)
  SELECT ID,NAME,ADDRESS FROM #tbl1a




Post a Comment

0 Comments