@echo off
setlocal
rem Change next lines
set TargetDSN=MyGlobalDB
set Login=user
set Password=password
set SourceDB=d:\DBfromFarAway.db
set BaseDir=\mydb
set PrimaryKeyColumn=BaseId
rem
rem
rem
rem DO NOT CHANGE ANYTHING BELOW THIS!!!
rem
rem
set SourceDBConnect=ENG=SourceDB;UID=%Login%;PWD=%Password%
set TargetDBConnect=DSN=%TargetDSN%;UID=%Login%;PWD=%Password%
if not exist %BaseDir%\temp mkdir %BaseDir%\temp
cd %BaseDir%\temp
for /F "tokens=2" %%i in ('date /T') do set A=%%i
for /F "tokens=1-3 delims=/" %%a in ("%A%") do set BackupDir=%BaseDir%\Backup_Before_Joining_%%c_%%a
mkdir %BackupDir%
echo Backing up target database into %BackupDir%
start /w dbisqlc -q -c "%TargetDBConnect%" BACKUP DATABASE DIRECTORY '%BackupDir%';STOP ENGINE UNCONDITIONALLY
echo create table #tbl(table_id integer, table_name char( 32 ), references_order integer default  0 ); >GetListOfTables.sql
echo insert into #tbl(table_id, table_name) >>GetListOfTables.sql
echo     select t.table_id, t.table_name >>GetListOfTables.sql
echo         from sys.systable t >>GetListOfTables.sql
echo             join sys.syscolumn c on t.table_id=c.table_id >>GetListOfTables.sql
echo         where t.table_type='BASE' and c.column_name='%PrimaryKeyColumn%' and creator= 1 ; >>GetListOfTables.sql
echo begin >>GetListOfTables.sql
echo     declare o integer; >>GetListOfTables.sql
echo     set o= 0 ; >>GetListOfTables.sql
echo     while o is not null loop >>GetListOfTables.sql
echo         update #tbl set references_order = o+ 1  >>GetListOfTables.sql
echo             from sys.sysforeignkey >>GetListOfTables.sql
echo             where foreign_table_id=#tbl.table_id and primary_table_id in >>GetListOfTables.sql
echo 				(select table_id from #tbl where references_order=o); >>GetListOfTables.sql
echo         if @@rowcount= 0  then set o=null; else set o=o+ 1 ; end if; >>GetListOfTables.sql
echo     end loop; >>GetListOfTables.sql
echo end; >>GetListOfTables.sql
echo select table_name from #tbl order by references_order, table_name; >>GetListOfTables.sql
echo output to ListOfTables.lst format fixed; >>GetListOfTables.sql
echo drop table #tbl; >>GetListOfTables.sql
echo Unloading tables from source database....
start /w dbisqlc -q -c "StartLine=dbeng9 -r -gk all -gl all -n SourceDB;DBF=%SourceDB%;ASTOP=NO;%SourceDBConnect%" GetListOfTables.sql
echo set temporary option date_format='yyyy-mm-dd';>unload.sql
for /F %%i in (ListOfTables.lst) do echo print 'unloading %%i';>>unload.sql
for /F %%i in (ListOfTables.lst) do echo UNLOAD TABLE "DBA"."%%i" TO '%%i.dat' APPEND OFF;>>unload.sql
echo STOP ENGINE UNCONDITIONALLY;>>unload.sql
start /w dbisqlc -q -c "%SourceDBConnect%" unload.sql
echo Loading tables into target database....
start /w dbisqlc -q -c "StartLine=dbeng9 -gk all -gl all;ASTOP=NO;%TargetDBConnect%" GetListOfTables.sql
echo set option date_format='yyyy-mm-dd';>load.sql
for /F %%i in (ListOfTables.lst) do echo print 'loading %%i';>>load.sql
for /F %%i in (ListOfTables.lst) do echo LOAD TABLE "DBA"."%%i" FROM '%%i.dat' CHECK CONSTRAINTS OFF;>>load.sql
for /F %%i in (ListOfTables.lst) do if not exist %%i.dat copy nul %%i.dat>nul
echo commit;>>load.sql
echo STOP ENGINE UNCONDITIONALLY;>>load.sql
start /w dbisqlc -q -c "%TargetDBConnect%" load.sql
echo y|del /F /Q *.*
cd ..
rd temp
echo Done.
pause
endlocal