Saturday, December 3, 2011

Bulk Upload in Sql server (using from OpenXML)

C# Code:

xml = " xml += " Element='SaveMyData' ";
xml += "EmpID='" + 1 + "' ";
xml += "EmpName='" + vijay + "' ";
xml += "EmpValue='" + 100 + "' />
";

IN SQL Server Stored Procedure:

CREATE PROCEDURE [dbo].[MyStoredProcedure]
@ActionType Varchar(20),
@XML text
AS
BEGIN
SET NOCOUNT ON
Declare @intRow int
Exec sp_xml_preparedocument @intRow Output, @xml
IF @ActionType ='Insert'
BEGIN

//Insert Query with Where condition

Insert into EmpEmployee (EmpID,EmpName,EmpValue)
Select xEmpID,xEmpName,xEmpValue
from OpenXML(@intRow,'/root/row[@Element="SaveMyData"]')
With
( xEmpID int '@EmpID', xEmpName varchar (50) '@EmpName', xEmpValue varchar (50) '@EmpValue', ) where xEmpID not in (select EmpID from EmpEmployee where EmpID=xEmpID,EmpName=xEmpName)

//Update Query with Where condition

Update EmpEmployee (EmpID=xEmpID,EmpName=xEmpName,EmpValue=xEmpValue)
from OpenXML(@intRow,'/root/row[@Element="SaveMyData"]') With ( xEmpID int '@EmpID', xEmpName varchar (50) '@EmpName', xEmpValue varchar (50) '@EmpValue', ) where EmpID=xEmpID and EmpName=xEmpName)

End
exec sp_xml_removedocument @intRow
END

No comments: