Answer: There are several ways to create a Data Transformation process. One way is a SQL script using the database's innate programming language. The main trick in our table structure is the AutoID column that is used in the detail table as foreign key. This means that after the header record is written, we need to ask the database for the ID it just assigned to the new record. Then we can take this ID and write it in the detail record.

Here is a sample SQL script that populates HIPAA Claim Master's tables with data from a fictitious other table. Just three fields are used to show the principle.

declare mycur cursor for select ID from OldTable
declare @iid int

open mycur
fetch next from mycur into @iid
while @@fetch_status = 0
begin

DECLARE @DataID bigint;
INSERT INTO dbo.EDI_Claims (TradingPartnerID, BillProvLast)
(SELECT TradingPartnerID, BillProvLast FROM OldTable WHERE OldTable.ID = @iid);
SELECT @DataID = scope_identity();
insert into EDI_ClaimDetail (ClaimID, LineNumber, Amount) (SELECT @DataID, '1', c.Amount FROM OldTable as c

WHERE c.ID = @iid);
fetch next from mycur into @iid ;
continue end

close mycur
deallocate mycur