DataTransfer should be able to create SQL query from AL code. Business Central already does a lot of this behind the scenes for all normal data operations so similar approach could be used for data migration as well.

Here is pseudo code how the feature would be used. Explanation is below the code:

Clear(DataTransfer);

DataTransfer.StartQueryRecorder();

DataTransfer.Include(VATEntry);

DataTransfer.Include(Vendor);

DataTransfer.Include(Customer);

VATEntry.Reset();

VATEntry.SetFilter(Type, '%1|%2', VATEntry.Type::Purchase, VATEntry.Type::Sale);

VATEntry.SetFilter("Bill-to/Pay-to No.", '<>%1', '');

foreach VATEntry do begin

  case VATEntry.Type of

    VATEntry.Type::Purchase:

      if Vendor.Get(VATEntry."Bill-to/Pay-to No.") then

        VATEntry."LBC Bill-to/Pay-to Name" := Vendor.Name;

    VATEntry.Type::Sale:

      if Customer.Get(VATEntry."Bill-to/Pay-to No.") then

        VATEntry."LBC Bill-to/Pay-to Name" := Customer.Name;

  end;       

  VATEntry.Modify(false);

end;

DataTransfer.StopQueryRecorder();

DataTransfer.RunQuery();

/*

Generated query:

UPDATE

  target

SET

  [LBC Bill-to/Pay-to Name] = COALESCE(

    (CASE target.[Type]

  WHEN 1 THEN (SELECT [Name] FROM [...$Vendor$...] t1 WHERE t1.[No_] = target.[Bill-to/Pay-to No_])

      WHEN 2 THEN (SELECT [Name] FROM [...$Customer$...] t2 WHERE t2.[No_] = target.[Bill-to/Pay-to No_])

    END),

    target.[LBC Bill-to/Pay-to Name])

FROM

  [...$VAT Entry$...] target

WHERE

  target.[Type] IN (1, 2) AND

  target.[Bill-to_Pay-to No_] <> ''

*/

The DataTransfer variables should have additional methods: StartQueryRecorder, Include, StopQueryRecorder and RunQuery.

StartQueryRecorder() would stop "normal" language mode and only allow basic operations with records: filtering, looping, getting a record, assigning fields, modifying record, deleting record and inserting record. Control flow should also be limited to foreach, case and if statements.

Include(SomeRecord) function would include tables to query. This tells query builder which variables are part of the query and will not have their value decided until the query runs.

StopQueryRecorder() would return the "normal" language mode and save the built query in the DataTransfer variable. It should be possible to preview the built SQL query in the debugger. StopQueryBuilder() should fail if exact table names, field names are not known at that point.

RunQuery() would execute the query stored in the DataTransfer variable. Unlike now, it should be possible to mix deletes, updates and inserts - it does not need to be a single SQL query.

Both Record ... and RecordRef datatypes should be "foreachable". This would be replacement for if then repeat until pattern that would simplify code analysis. "If then repeat until" should also work to maintain compatibility with older BC versions.

DataTransfer variable should also be able to deal with RecordRefs. For example:

// Suppose. this information comes from somewhere else.

SomeTableNo := Database::Item;

SomeFieldNo := Item.FieldNo("SOME Field");

SomeConstant := 'SOME-CONST';

Clear(DataTransfer);

DataTransfer.StartQueryRecorder();

DataTransfer.Include(RecordRef);

Clear(RecordRef);

RecordRef.Open(SomeTableNo); // it is not clear which table it will be at the compile time

if RecordRef.FindSet() then // this loop form should be allowed too

repeat

RecordRef.Field(SomeFieldNo).Value(SomeConstant); // it is not clear which field will be set at BC app compile time, but exact field is known at runtime

RecordRef.Modify(false);

until RecordRef.Next() = 0;

RecordRef.Close();

DataTransfer.StopQueryRecorder();

DataTransfer.RunQuery();

/*

Generated query:

UPDATE

target

SET

[SOME Field] = 'SOME-CONST'

FROM

[...$Item$...] target

*/

I don't think this feature should be limited to data migrations. BC sorely lacks runtime query builder - we do have query object, but that is too inflexible. Imagine writing this in some report:

Clear(QueryBuilder);

QueryBuilder.StartQueryRecorder();

QueryBuilder.Include(GLEntry);

QueryBuilder.Include(TempBuffer);

GLEntry.Reset();

GLEntry.SetRange("Posting Date", 0D, ToDate);

GLEntry.Group("G/L Account No.");

GLEntry.Sum(GLEntry.Amount);

foreach GLEntry do begin

// DataTransfer to temporary table

TempBuffer.Init();

TempBuffer."G/L Account No." := GLEntry."G/L Account No.";

TempBuffer.Amount := GLEntry.Amount;

TempBuffer.Insert(false);

end;

QueryBuilder.StopQueryRecorder();

QueryBuilder.RunQuery();

The only remaining problem is backwards compatibility. There is no reason whatsoever to limit this feature to the latest BC runtime. QueryBuilder should be a codeunit that uses dotnet "magic" to work with BC insides. That way the AL syntax does not need any changes - compile time checks are already done by the compiler.

AL compiler could even lend BC a hand and rewrite code between StartBuildingQuery and StopBuildingQuery to be anything any BC runtime would need, e.g. code instructions how to build the query. Previous query could be rewritten like this:

Clear(QueryBuilder);

#if USE_QUERY_RECORDER

QueryBuilder.StartQueryRecorder();

QueryBuilder.Include(GLEntry);

QueryBuilder.Include(TempBuffer);

GLEntry.Reset();

GLEntry.SetRange("Posting Date", 0D, ToDate);

GLEntry.Group("G/L Account No.");

GLEntry.Sum(GLEntry.Amount);

foreach GLEntry do begin

// DataTransfer to temporary table

TempBuffer.Init();

TempBuffer."G/L Account No." := GLEntry."G/L Account No.";

TempBuffer.Amount := GLEntry.Amount;

TempBuffer.Insert(false);

end;

QueryBuilder.StopQueryRecorder();

#else

QueryBuilder.StartQueryBuilder();

QueryBuilder.Include('GLEntry', GLEntry);

QueryBuilder.Include('TempBuffer', TempBuffer);

QueryBuilder.SetFilter('GLEntry', GLEntry, GLEntry.FieldNo("Posting Date"), '%1..%2', 0D, ToDate);

QueryBuilder.Group('GLEntry', GLEntry, GLEntry.FieldNo("G/L Account No_"));

QueryBuilder.Sum('GLEntry', GLEntry, GLEntry.FieldNo(Amount));

QueryBuilder.BeginForeach('GLEntry', GLEntry);

QueryBuilder.Init('TempBuffer', TempBuffer);

QueryBuilder.Set('TempBuffer', TempBuffer, TempBuffer.FieldNo("G/L Account No."), 'GLEntry', GLEntry, GLEntry.FieldNo("G/L Account No."));

QueryBuilder.Set('TempBuffer', TempBuffer, TempBuffer.FieldNo("G/L Account No."), 'G/Entry', GLEntry, GLEntry.FieldNo(Amount));

QueryBuilder.Insert('TempBuffer', TempBuffer);

QueryBuilder.EndForeach();

QueryBuilder.StopQueryBuilder();

#endif

QueryBuilder.RunQuery();

Thanks.