感谢各位的热心!
我一般把所有.pas放在一个路径下,每天下班前做一次备份,而备份前是先把.dsk删除的。
备份的路径是在另一个分区上,所有不会编译象hpretty朋友所说的"过时的文件"。
我调试出是因为下面的函数有问题,可我实在不知道什么原因。
function TfmDataBackup.BackupATab(const asDstDBName, asTabName: string;
abIsCoverOld, abIsTableExist: Boolean; const aiRecCnt: Integer): Boolean;
var
sFullDstTabName, sFullTabName, sTabKey, sSQL, sFields, sFieldsWithAlias: string;
i: Integer;
ssFields, ssSQLs: TStrings;
bHasIndentity: Boolean;
begin
//下例为表employees的 startdate 列解除规则绑定。EXEC sp_unbindrule 'employees.startdate'
//Get fields string like: string1,string2,string3...
sFields := '';
ssFields := TStringList.Create;
try
ssFields.Clear;
dmDTMMain.ADOConnSQLSVR.GetFieldNames(asTabName, ssFields);
for i := 0 to ssFields.Count - 1 do
begin
sFields := sFields + ssFields + ',';
sFieldsWithAlias := sFieldsWithAlias + 'B.' + ssFields + ',';
end;
Delete(sFields, Length(sFields), 1);
Delete(sFieldsWithAlias, Length(sFieldsWithAlias), 1);
finally
ssFields.Free;
end;
sTabKey := dmDTMMain.GetTablePrimaryKey(asTabName);
sFullTabName := dmDTMMain.ADOConnSQLSVR.DefaultDatabase + '.DBO.' + asTabName;
sFullDstTabName := asDstDBName + '.DBO.' + asTabName;
bHasIndentity := dmDTMMain.TableHasIdentity(sFullTabName);
ssSQLs := TStringList.Create;
try
if sTabKey <> '' then
begin
if abIsTableExist then
begin
if bHasIndentity then
ssSQLs.Add(Format('SET IDENTITY_INSERT %S ON', [sFullDstTabName]));
if abIsCoverOld then
begin
if aiRecCnt = 0 then
begin
//DELETE GPS_GIS.DBO.S_FUNCList WHERE GPS_GIS.DBO.S_FUNCList.FuncID IN (SELECT FuncID FROM AAA.DBO.new_table
sSQL := Format('DELETE %S WHERE %S.%S IN (SELECT %S FROM %S)',[sFullTabName, sFullTabName, sTabKey, sTabKey, asDstDBName + '.DBO.' + asTabName]);
ssSQLs.Add(sSQL);
sSQL := Format('INSERT %S(%S) SELECT DISTINCT %S FROM %S A, %S B',[sFullDstTabName,sFields,sFieldsWithAlias,sFullDstTabName,sFullTabName]);
ssSQLs.Add(sSQL);
end else
begin
sSQL := Format('DELETE %S WHERE %S.%S IN (SELECT TOP %D %S FROM %S)',[sFullTabName, sFullTabName, sTabKey, aiRecCnt, sTabKey, sFullDstTabName]);
ssSQLs.Add(sSQL);
sSQL := Format('INSERT %S(%S) SELECT DISTINCT TOP %D %S FROM %S A, %S B',[sFullDstTabName,sFields,aiRecCnt,sFieldsWithAlias,sFullDstTabName,sFullTabName]);
ssSQLs.Add(sSQL);
end;
{ INSERT AAA.DBO.new_table(FuncID, FuncName, FormName, Tag) SELECT DISTINCT B.FuncID, B.FuncName, B.FormName, B.Tag
FROM AAA.DBO.new_table A, GPS_GIS.DBO.S_FUNCList B WHERE B.FuncID NOT IN (SELECT FuncID FROM AAA.DBO.new_table)}
end else //if abIsCoverOld then
begin
if aiRecCnt = 0 then
sSQL := Format( 'INSERT %S(%S) SELECT DISTINCT %S FROM %S A, %S B WHERE B.%S NOT IN(SELECT %S FROM %S)',[sFullDstTabName,sFields,sFieldsWithAlias,sFullDstTabName,sFullTabName,sTabKey,sTabKey,sFullDstTabName])
else
sSQL := Format( 'INSERT %S(%S) SELECT DISTINCT TOP %D %S FROM %S A, %S B WHERE B.%S NOT IN(SELECT %S FROM %S)',[sFullDstTabName,sFields,aiRecCnt,sFieldsWithAlias,sFullDstTabName,sFullTabName,sTabKey,sTabKey,sFullDstTabName]);
ssSQLs.Add(sSQL);
end;
if bHasIndentity then
ssSQLs.Add(Format('SET IDENTITY_INSERT %S OFF', [sFullDstTabName]));
end else //if abIsExist then...
begin
if aiRecCnt = 0 then
sSQL := Format('SELECT * INTO %S FROM %S WHERE 1<>2', [sFullDstTabName,sFullTabName])
else
sSQL := Format('SELECT TOP %D * INTO %S FROM %S WHERE 1<>2', [aiRecCnt,sFullDstTabName,sFullTabName]);
ssSQLs.Add(sSQL);
end;
end else //如果表没有主键...
begin
if abIsTableExist then
begin
if aiRecCnt = 0 then
begin
sSQL := Format( 'INSERT %S(%S) SELECT DISTINCT %S FROM %S A, %S B',
[sFullDstTabName,sFields,sFieldsWithAlias,sFullDstTabName,sFullTabName])
end else
sSQL := Format( 'INSERT %S(%S) SELECT DISTINCT TOP %D %S FROM %S A, %S B',
[sFullDstTabName,sFields,aiRecCnt,sFieldsWithAlias,sFullDstTabName,sFullTabName]);
end else
begin
if aiRecCnt = 0 then
begin
sSQL := Format('SELECT * INTO %S FROM %S WHERE 1=2', [sFullDstTabName,sFullTabName])
end else
sSQL := Format('SELECT TOP %D * INTO %S FROM %S WHERE 1=2', [aiRecCnt,sFullDstTabName,sFullTabName]);
end;
ssSQLs.Add(sSQL);
end;
Result := dmDTMMain.ExecBatSQL(ssSQLs);
finally
ssSQLs.Free;
end;
end;