CODEWITHSUNDEEP
sql-serverstored-proceduresbiztalkbiztalk-2013
Sid
Sid

Reputation: 117

Inserting/Updating a SQL Server table using stored procedure in BizTalk

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

enter image description here

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.

Upvotes: 3

Views: 935

Answers (2)

Roshan Tolwani
Roshan Tolwani

Reputation: 78

Use table type as a parameter for the SP, instead of passing individually. We can use looping functoid if we use 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 infor on how to use table value parameter check out this link:- https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine

Upvotes: 0

Chuck
Chuck

Reputation: 1031

Here I converted the separate update and insert 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

Upvotes: 2

Related Questions