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

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.