I am currently working on getting a set of records from a view in the Oracle database and trying to insert/update them in to the table in the SQL Server table depending on a column using BizTalk.
For this I created a stored procedure:
Create PROCEDURE [dbo].[uspInsertorUpdateDepartment]
@dept_name varchar(64),
@jax_dept_id char(32)
AS
BEGIN
SET NOCOUNT ON;
IF (SELECT TOP (1) 1 FROM afm.[jax_dept]
WHERE jax_dept_id = @jax_dept_id) IS NULL
INSERT INTO afm.[jax_dept](dept_name, jax_dept_id)
VALUES (@dept_name,@jax_dept_id)
ELSE
UPDATE afm.[jax_dept]
SET dept_name = @dept_name
WHERE jax_dept_id = @jax_dept_id
END
I created the schema for the stored procedure using consume adapter service. Used them in the mapping and the orchestration. Though I was not able to use the lopping functoid in the mapping
So removed the lopping and deployed the application. And tried to run and it ran without any error but just insert the first record from the oracle view in to the SQL Server database leaving all the other records. How can this be approached so the entire set of records from the oracle is inserted/updated in to SQL Server database.