SAP CPI - JDBC Adapter - Upsert Single & Bulk using Direct SQL, Splitter and Stored Procedure with XML Payload without Looping

[yeeloon-khoo]

Hello CPI Integrators,

This blog post focus on SAP CPI JDBC UPSERT operation, below videos show example on how to perform single upsert and bulk upsert, Direct SQL and stored procedure approach, and Splitter looping and Stored procedure XML Bulk Upsert without Looping (even inside stored procedure).

SAP CPI - JDBC Adapter - UPSERT Single & Bulk using Direct SQL & Stored Procedure with XML Payload:



SAP CPI - JDBC Adapter - Performance Upsert Each Row using Splitter vs Upsert using XML Bulk Upsert:



Below are SQL/Stored procedure used in above videos:

CREATE TABLE - Direct SQL (you can use this to create same table to try out examples):

CREATE TABLE [dbo].[SpecialCustomers](

	[CustomerID] [nchar](5) NOT NULL,

	[CompanyName] [nvarchar](40) NOT NULL

 CONSTRAINT [PK_SpecialCustomers] PRIMARY KEY CLUSTERED 

(

	[CustomerID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

 

UPSERT - Single Record - Direct SQL:

UPDATE SpecialCustomers SET CompanyName = 'A Name from Update'

WHERE CustomerID = 'AAAAA';

IF @@ROWCOUNT = 0

BEGIN

    INSERT INTO SpecialCustomers (CustomerID, CompanyName)

    VALUES ('AAAAA', 'A Name from Insert');

END

 

Create Stored Procedure Upsert_SpecialCustomer:

CREATE PROCEDURE Upsert_SpecialCustomer

	@CustomerID varchar(5),

	@CompanyName varchar(40)

AS

BEGIN

	UPDATE SpecialCustomers SET CompanyName = @CompanyName

	WHERE CustomerID = @CustomerID;

	IF @@ROWCOUNT = 0

	BEGIN

		INSERT INTO SpecialCustomers (CustomerID, CompanyName)

		VALUES (@CustomerID, @CompanyName);

	END

END

 

EXECUTE UPSERT - Single Record - Direct SQL :

Upsert_SpecialCustomer @CustomerID = 'AAAAA', @CompanyName = 'A Name Upsert';

 

EXECUTE UPSERT - Single Record - XML SQL :

<root>

    <statement>

        <storedProcedureName action="EXECUTE">

            <table>Upsert_SpecialCustomer</table>

            <CustomerID isInput="true" type="VARCHAR">AAAAA</CustomerID>

            <CompanyName isInput="true" type="VARCHAR">A Name from SP</CompanyName>

        </storedProcedureName>

    </statement>

</root>

 

Create Stored Procedure XMLBatchUpsert_SpecialCustomers :

CREATE PROCEDURE XMLBatchUpsert_SpecialCustomers

	@XMLData XML

AS 

BEGIN 

	SELECT

	  CUST.item.value('(CustomerID)[1]', 'varchar(5)') as CustomerID, 

	  CUST.item.value('(CompanyName)[1]', 'varchar(40)') as CompanyName

	INTO #TempSpecialCustomers

	FROM 

	  @XMLData.nodes('/root/item') as CUST(item)



	UPDATE SpecialCustomers

	SET CompanyName = T.CompanyName

	FROM SpecialCustomers AS C, #TempSpecialCustomers AS T

	WHERE C.CustomerID = T.CustomerID



	INSERT INTO SpecialCustomers (CustomerID, CompanyName)

	SELECT CustomerID, CompanyName

	FROM #TempSpecialCustomers AS T

	WHERE NOT EXISTS(SELECT * FROM SpecialCustomers WHERE CustomerID = T.CustomerID)

END

 

EXECUTE UPSERT - Multiple Record - XML SQL:

<root>

    <statement>

        <storedProcedureName action="EXECUTE">

            <table>XMLBatchUpsert_SpecialCustomers</table>

            <XMLData isInput="true" type="CLOB"><![CDATA[<root>

	<item>

		<CustomerID>AAAAA</CustomerID>

		<CompanyName>AA Name</CompanyName>

	</item>

	<item>

		<CustomerID>BBBBB</CustomerID>

		<CompanyName>BB Name</CompanyName>

	</item>

	<item>

		<CustomerID>CCCCC</CustomerID>

		<CompanyName>CC Name</CompanyName>

	</item>

    <item>

		<CustomerID>DDDDD</CustomerID>

		<CompanyName>DD Name</CompanyName>

	</item>

</root>]]></XMLData>

        </storedProcedureName>

    </statement>

</root>

 

Hope you found above content useful.

 

Above videos from my published SAP CPI course. If interested on other CPI JDBC lessons or CPI topics, can check out the course at this link. Thanks.