TFDBatchMoveSQLWriter and table structure updates

[Stéphane Wierzbicki 45]

Hello

I transfer Excel data to an MS SQL database. To do this I use the TFDMemTable, TFDBatchMove and TFDBatchMoveSQLWriter components. 

The TFDMemTable field definitions are automatically created according to the Excel file (Excel columns and data type are used to create the field definitions).
 
The name of the Excel file is used to fill in the Tablename property of the TFDBatchMoveSQLWriter component.

When the TFDBatchMove is executed, the "TableName" table is created if it does not exist in the MS SQL database. If the table does exist, the data is inserted into it. This is perfect.

I have a problem when the Excel file evolves and especially when a new column appears. TFDBatchMoveSQLWriter does not update the SQL table structure and the new field(s) are not created.

 

Anyone have an idea to help me solve this problem? Maybe should I use another FireDac for this purpose?

Here is my function

 

 

function StoreDataset(aFDConnection: TFDConnection; aDataset: TDataSet;
  aTableName: string = ''; aOptions: TFDBatchMoveOptions = [poIdentityInsert,
  poCreateDest, poSkipUnmatchedDestFields, poUseTransactions];
  aMode: TFDBatchMoveMode = dmAlwaysInsert): boolean;
var
  FFDTable: TFDTable;
  FBatchmove: TFDBatchMove;
  FBatchmoveDatasetReader: TFDBatchMoveDataSetReader;
  FBAtchMoveSQLWriter: TFDBatchMoveSQLWriter;

begin
  Result := False;

  if not assigned(aFDConnection) then
    raise Exception.Create('Please provide a Connection');

  if not assigned(aDataset) then
    raise Exception.Create('Please provide a Dataset');

  if aTableName = '' then
    aTableName := aDataset.Name;

  if aTableName = '' then
    raise Exception.Create('Please provide a table name');

  FBatchmove := TFDBatchMove.Create(nil);
  FBatchmoveDatasetReader := TFDBatchMoveDataSetReader.Create(nil);
  FBAtchMoveSQLWriter := TFDBatchMoveSQLWriter.Create(nil);
  FFDTable := TFDTable.Create(nil);
  try

    FBatchmove.Reader := FBatchmoveDatasetReader;
    FBatchmove.Writer := FBAtchMoveSQLWriter;
    FBatchmove.Options := aOptions;
    FBatchmove.Mode := aMode;
    FBAtchMoveSQLWriter.Connection := aFDConnection;
    aFDConnection.Connected := True;

    FBatchmoveDatasetReader.Dataset := aDataset;
    FBAtchMoveSQLWriter.TableName := aTableName;
    try
      FBatchmove.Execute;
      Result := True;
    except
      on e: Exception do
      begin
        Result := False;
        TMSLogger.Exception(e.message);
      end;
    end;

  finally
    aFDConnection.Connected := False;
    FBatchmove.Free;
    FBatchmoveDatasetReader.Free;
    FBAtchMoveSQLWriter.Free;
    FFDTable.Free;
  end;
end;

 

 

[Uwe Raabe 2057]

Can't you just delete the table before executing the batch move? 

[Stéphane Wierzbicki 45]

15 hours ago, Uwe Raabe said:

Can't you just delete the table before executing the batch move? 

No, I'm sorry, I'm receiving those files every month and data needs to be appended into the same table. 

Edited December 21, 2019 by Stéphane Wierzbicki

[Uwe Raabe 2057]

OK, there is an undocumented option poSkipUnmatchedDestFields which you can set to False (although I don't know what it does).

[Stéphane Wierzbicki 45]

9 hours ago, Uwe Raabe said:

OK, there is an undocumented option poSkipUnmatchedDestFields which you can set to False (although I don't know what it does).

I forgot to tell you that I've already tried this. Removing from the the options set did not changed any thing. 

[Dmitry Arefiev 101]

TFDBatchMoveSQLWriter cannot alter table structure, it can only create a new table.

[Stéphane Wierzbicki 45]

24 minutes ago, Dmitry Arefiev said:

TFDBatchMoveSQLWriter cannot alter table structure, it can only create a new table.

Thanks Dmitry, do you have some advice on how to get this done in another way ? 

Edited December 21, 2019 by Stéphane Wierzbicki

[Attila Kovacs 629]

@Stéphane Wierzbicki It's not that easy as it sounds. What if a column name changes? Is it a new column or just the name changed? What if a datatype changes? etc..etc..

 

Anyway, I can't even imagine what can you do with this kind of data in an SQL Server where columns are occasionally added. Either you should unpivot the excel before importing it or storing the file in a blob.

[Stéphane Wierzbicki 45]

22 hours ago, Attila Kovacs said:

@Stéphane Wierzbicki It's not that easy as it sounds. What if a column name changes? Is it a new column or just the name changed? What if a datatype changes? etc..etc..

It's not 😉, I know. I've implemented some checks before uploading data. I do have a mapping table used to control XLS file. XLS file is not uploaded if there is a change in the header (a column name changed for example). 

 

Problem is that I need to both update the mapping table and to create new fields in my database table. I wanted the later to be automatically handled by Tfdbatchmove component.