I have mapped complex EDi schema to SQL database by looping in orchestration.Thats the usuall process i was following till WCF sql came into picture.Suppose you have 2 relational database with primary and foreignkey relationship and you have a complex schema with multiple batch of records.How could we solve this kind of issue without using loop shape in orchestration

Let me explain the issue again:
I have 2 tables like this below



Now i have the source schema look like this




I used following steps to solve the above issue.Hope it may help someone.

1.Create the store procedure for both of tables
1st create the table type and then create the stroed procedure and pass parameter as tabletype

create type BizTalkSWRequestTableType as Table
(
RequestId int not null,
invocationPoint nvarchar(50),
requestType nvarchar(50),
claimNumber nvarchar(50),
treatmentsettingscode nvarchar(50),
memberID nvarchar(50),
memberName nvarchar(50),
lastName nvarchar(50),
firstName nvarchar(50),
middleName nvarchar(50),
dateOfBirth nvarchar(50),
clientCode nvarchar(50),
EnrollmentgroupID nvarchar(50),
EnrollmentgroupName nvarchar(50),
EnrollmentplanCode nvarchar(50),
EnrollmenteffectiveDate nvarchar(50),
EnrollmentterminationDate nvarchar(50),
EnrollmentsubscriberID nvarchar(50),
EnrollmentisSubscriber nvarchar(50),
FacilityexternalID nvarchar(50),
FacilityproviderName nvarchar(50),
Facilityrole nvarchar(50),
FacilitytypeCode nvarchar(50),
FacilityprimarySpecialtyCode nvarchar(50),
FacilitynationalProviderID nvarchar(50)

);


create type BiztalkSWInpatientROTableType as Table
(
RequestId int not null,
InpatientOutcomeID nvarchar(50) not null,
statuscode nvarchar(50),
reasoncode nvarchar(50),
bedtypecode nvarchar(50),
certifiedUnits nvarchar(50),
fromDate nvarchar(50),
throughDate nvarchar(50),
standardRate nvarchar(50),
negotiatedRate nvarchar(50)

);

drop procedure InsertPatientDetails

create procedure InsertPatientDetails
(
@PatientRequest as BizTalkSWRequestTableType readonly,
@patientRvwOut as BiztalkSWInpatientROTableType readonly
)
as
begin

insert into BizTalkSWRequestTable
(
RequestId ,
invocationPoint ,
requestType ,
claimNumber ,
treatmentsettingscode ,
memberID ,
memberName ,
lastName ,
firstName ,
middleName ,
dateOfBirth ,
clientCode ,
EnrollmentgroupID ,
EnrollmentgroupName ,
EnrollmentplanCode ,
EnrollmenteffectiveDate ,
EnrollmentterminationDate ,
EnrollmentsubscriberID ,
EnrollmentisSubscriber ,
FacilityexternalID ,
FacilityproviderName ,
Facilityrole ,
FacilitytypeCode ,
FacilityprimarySpecialtyCode ,
FacilitynationalProviderID
)
select
RequestId ,
invocationPoint ,
requestType ,
claimNumber ,
treatmentsettingscode ,
memberID ,
memberName ,
lastName ,
firstName ,
middleName ,
dateOfBirth ,
clientCode ,
EnrollmentgroupID ,
EnrollmentgroupName ,
EnrollmentplanCode ,
EnrollmenteffectiveDate ,
EnrollmentterminationDate ,
EnrollmentsubscriberID ,
EnrollmentisSubscriber ,
FacilityexternalID ,
FacilityproviderName ,
Facilityrole ,
FacilitytypeCode ,
FacilityprimarySpecialtyCode ,
FacilitynationalProviderID
from @PatientRequest

insert into BiztalkSWInpatientROTable
(
RequestId ,
InpatientOutcomeID ,
statuscode ,
reasoncode ,
bedtypecode ,
certifiedUnits ,
fromDate ,
throughDate ,
standardRate ,
negotiatedRate
)

select
RequestId ,
InpatientOutcomeID ,
statuscode ,
reasoncode ,
bedtypecode ,
certifiedUnits ,
fromDate ,
throughDate ,
standardRate ,
negotiatedRate

from @patientRvwOut
end

2.Now generate the schema from the addGenerated wizard



3.Now you will see 2 schemas and 1 binding file
a.InsertInPatientDetailsTableType.dbo.xsd
b.InsertInPatientDetailsTypedProcedure.dbo.xsd
c.WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml

4.Now create the maps from sources schema and InsertInPatientDetailsTypedProcedure.dbo.xsd



5.Now as usual process create the orchestration and look at the operation name in sql request response logical port.In my case it is "SQLReqResp"



6.make the application strong named and deploy the application and then in biztalk administration console from your application import the binding file "WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml" that have been created by add adapter wizard


7.Look for WCF-sql port configuration and go to configure option and change the operation name there.



8.Now create the receive and send port and start the application.You will see the result in database.