Looping and storing the Records in SQL server Using SQL Adapter

Posted by Rkbhuvanagiri under BizTalk Server on 12/2/2009 | Views : 3089 | Status : [Member] | Replies : 1
I have a Order.txt file which Contains the Header and 10,000 Records as Body and also Trailer Part. I have Three tables in my SQL Server. Header_Data, Body_Data and Trailer_Data. Now My Requirement is First i need to insert the header in Header_Data and i need to pick the Primary Key and insert that body (each record ) with primaryKey (Here in Body_Data the primary key will become Foreign key) record by record and same case for Trailer too. I used updategram it was working fine 100 records with bulk insertion but i need to loop through the 10,000 records and insert in to database.

is there any way to do this using loop shape and SQL adapter stored Procedures?

Please help me


Posted by: Virendradugar on: 12/2/2009 [Member] [MVP] Silver

I suggest you to create an xml of text file and pass xml to the store procedure.

In your store procedure, you can use

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument   

to iterate through the data.

Virendra Dugar

Rkbhuvanagiri, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response