Insert / Update SQL Server Table Using Stored Procedure in BizTalk
I am currently working on getting a recordset from a view in an Oracle database and trying to insert / update them into a SQL Server table based 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 a schema for a stored procedure using the consumption adapter service. Used them in cartography and orchestration. Although I was unable to use the lopping functionality in the display
So I removed the lopping and deployed the application. And tried to start and it started without any error, but just insert the first record from the oracle window into the SQL Server database, leaving all other records. How can this be approached so that the entire recordset from the oracle is inserted / updated into the SQL Server database.
source to share
Here I have converted a separate update and pasted into one merge statement:
Create PROCEDURE [dbo].[uspInsertorUpdateDepartment]
@dept_name varchar(64),
@jax_dept_id char(32)
AS
BEGIN
SET NOCOUNT ON;
merge afm.[jax_dept] as target
using (select @dept_name as dept_name, @jax_dept_id as jax_dept_id) as source
on source.jax_dept_id = target.jax_dept_id
when matched then
update target
SET dept_name = @dept_name
when not matched then
insert (dept_name, jax_dept_id)
values (@dept_name,@jax_dept_id)
;
END
source to share
Use table type as parameter to SP, not pass it separately. We can use looping functoid if we use the User Defined Table value as a parameter.
CREATE TYPE dbo.SampleType AS TABLE
(
dept_name varchar(64) not null,
jax_dept_id char(32) not null
)
---
Create PROCEDURE [dbo].[uspInsertorUpdateDepartment]
@TVP dbo.SampleType READONLY
AS
BEGIN
SET NOCOUNT ON;
--your insert or update query
For more information on how to use the table value parameter, visit this link: - https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database -engine
source to share