Create and copy table from db to another

[bwilt]

Hello,
I have Unidac 5.0.1 connected to a MDB.

I was wanting to copy a table from one MDB to another database which does not contain the table.

Is there a method to create the database schema from one database to another ?

Previously I had used CreateTable()

[murkay]

I use to create table in another msaccess database

UniConnection.ExecSQL('SELECT * INTO [' + DatabaseName + '].' + kod +
' FROM ' + kod + ' WHERE 0 = 1;');
Databasename is destination msaccess database name with full path.
UniConnection is your source connection.
kod is table name.

To copy you have many ways.
CRBatchMove , Uniloader
or
UniConnection.ExecSQL('INSERT INTO [' + DatabaseName + '].' + kod + ' SELECT * FROM ' + kod);

[AlexP]

Hello,

To retrieve DDL and data transfer, you can use the following code, this code demonstrates retrieving of field names, types and size. To retrieve other data (keys, default values, etc.), you can modify the code by specifying the needed MetaDataKind

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  Uni,
  AccessUniProvider,
  CRBatchMove;

const
  TableName = 'test';

var
  UniConnectionSrc, UniConnectionDst: TUniConnection;
  UniTableSrc, UniTableDst: TUniTable;
  UniMetaDataSrc: TUniMetaData;
  BatchMove: TCRBatchMove;
  SQL: string;
begin
  UniConnectionSrc := TUniConnection.Create(nil);
  try
    UniConnectionSrc.Connect('ProviderName=Access;Database=d:\src.MDB');
    UniMetaDataSrc := TUniMetaData.Create(nil);
    try
      UniMetaDataSrc.Connection := UniConnectionSrc;
      UniMetaDataSrc.MetaDataKind := 'Columns';
      UniMetaDataSrc.Restrictions.Values['Table_Name'] := TableName;
      UniMetaDataSrc.Open;
      SQL := 'CREATE TABLE ' + TableName + ' ('#13#10;
      while not UniMetaDataSrc.Eof do begin
        SQL := SQL + UniMetaDataSrc.FieldByName('COLUMN_NAME').AsString + ' ' +
                     UniMetaDataSrc.FieldByName('DATA_TYPE').AsString;
        if Pos('CHAR', UniMetaDataSrc.FieldByName('DATA_TYPE').AsString) > 0 then
          SQL := SQL + '(' + UniMetaDataSrc.FieldByName('DATA_LENGTH').AsString + ')';
        UniMetaDataSrc.Next;
        if not UniMetaDataSrc.Eof then
          SQL := SQL + ',';
        SQL := SQL + #13#10;
      end;
      SQL := SQL + ')';
    finally
      UniMetaDataSrc.Free;
    end;
    UniConnectionDst := TUniConnection.Create(nil);
    try
      UniConnectionDst.Connect('ProviderName=Access;Database=d:\Dst.MDB');
      UniConnectionDst.ExecSQL(SQL);
      UniTableSrc := TUniTable.Create(nil);
      try
        UniTableSrc.Connection := UniConnectionSrc;
        UniTableSrc.TableName := TableName;
        UniTableSrc.Open;
        UniTableDst := TUniTable.Create(nil);
        try
          UniTableDst.Connection := UniConnectionDst;
          UniTableDst.TableName := TableName;
          UniTableDst.Open;
          BatchMove := TCRBatchMove.Create(nil);
          try
            BatchMove.Source := UniTableSrc;
            BatchMove.Destination := UniTableDst;
            BatchMove.Execute;
          finally
            BatchMove.Free;
          end;
        finally
          UniTableDst.Free;
        end;
       finally
        UniTableSrc.Free;
      end;
    finally
      UniConnectionDst.Free;
    end;
  finally
    UniConnectionSrc.Free;
  end;
end.