Page 1 of 1
Create and copy table from db to another
Posted: Wed 07 Aug 2013 01:51
by 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()
Re: Create and copy table from db to another
Posted: Thu 08 Aug 2013 10:02
by 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);
Re: Create and copy table from db to another
Posted: Thu 08 Aug 2013 10:09
by 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.