1) Create Table type in sql server.
Create TYPE [eOrders] as TABLE(
[eCartId] [int] NOT NULL,
[ShippingMethodName] [Varchar](50) NULL,
[CustomerPO] [varchar](30) NULL,
[ShipToName] [varchar](50) NULL,
[ShipToAddress1] [varchar](50) NULL,
[ShipToAddress2] [varchar](50) NULL,
[ShipToCity] [varchar](50) NULL,
[ShipToStateZip] [varchar](50) NULL,
[Optional1] [varchar](255) NULL
)
Create TYPE [eOrderItems] as TABLE(
CustomerPO [varchar](50) NULL ,
LineNumber int null,
Quantity int null,
Vendor char(3) null,
PartNumber varchar(50) null,
UnitPrice varchar(50) null,
Optional1 varchar(500) null,
Optional2 varchar(500) null,
Optional3 varchar(500) null,
Optional4 varchar(500) null
)
2) Create Insert Stored procedure.
create Procedure [InsertOrder]
(
@Orders as [Orders] READONLY,
@OrderItems [OrderItems]] READONLY
)
as
Begin
Declare @OrderID int
Insert into Orders (eCartID , ShippingMethodID, CustomerPO, ShipToName, ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToZip)
Select PurchaseOrderNo ShipToName , ShipToAddress1 , ShipToAddress2 , ShipToCity, ShipToStateZip
from @Order
set @OrderID = scope_identity()
Insert into OrderItems (CustomerPO, LineNumber , Quantity , Vendor , PartNumber, UnitPrice, Optional1, Optional2 ,Optional3 , Optional4)
Select CustomerPO, LineNumber , Quantity , Vendor , PartNumber, UnitPrice, Optional1, Optional2 ,Optional3 , Optional4
Price from @OrderItems
End
3) let's move to biztalk environment.
Create Insert schema using Add Generated Schema diglogue. Click on Add -> Add Generated schema -> Consume Adapter Service -> Consume WCF service
AFter then select your stored procedure. The schema will be generated as below.
4) After then Create Map to map your data with Insert schema as below.
5) Construct Insert message in orchestration and able to insert records using table type in single trip.
