INSERT INTO SELECT ohne Spaltenliste

[Bummi]

zum fertigbasteln:

Delphi-Quellcode:

Function GetTableValueScript(Ads:TAdodataset;const tableName:String;HasIdentity:Boolean):String;
var

  FieldNames:TStringList;
  Values:TStringList;
  i:Integer;
  FieldNamesStr:String;
  Function Prepare(f:TField):String;
    begin
       if f.IsNull then Result := 'NULL'
       else
       Case
         f.DataType of
          ftSmallint, ftInteger, ftWord,ftAutoInc,ftLargeint,
          ftFloat, ftCurrency, ftBCD ,ftFMTBcd,ftLongWord, ftShortint, ftByte, ftExtended ,ftSingle: Result := Stringreplace(f.AsString, ',','.',[]) ;
          ftString,ftWideString,ftGuid : Result := QuotedStr(f.AsString);
          ftDate, ftDateTime, ftTime : Result := QuotedStr(FormatdateTime('yyyymmdd hh:nn:ss.zzz',f.AsDateTime));
          ftBoolean: Result := IntToStr(Integer(f.AsBoolean));
          // to be continued
       End;
    end;
  Function GetValues:String;
    begin
        Result := StringReplace(Values.Text,#13#10,', ',[rfReplaceAll]);
        Result := Copy(Result,1,Length(Result) - 2);
    end;
begin

  FieldNames:=TStringList.Create;
  Values:=TStringList.Create;
  try
  Ads.CommandText := 'Select top 100 * from ' + tablename;
  Ads.Open;
  for I := 0 to Ads.FieldCount -1 do FieldNames.Add('[' + Ads.Fields[i].FieldName + ']');
  FieldNamesStr := StringReplace(FieldNames.Text,#13#10,', ',[rfReplaceAll]);
  FieldNamesStr := Copy(FieldNamesStr,1,Length(FieldNamesStr) - 2);
  while not Ads.Eof do
    begin
      Values.Clear;
      for I := 0 to Ads.FieldCount -1 do Values.Add(Prepare(Ads.Fields[i]));
      Ads.Next;
      Result := Result + 'Insert Into ' + tablename + '(' + FieldNamesStr + ') VALUES (' + GetValues + ')'  + #13#10#13#10;
    end;
  finally
    FieldNames.Free;
    Values.Free;
  end;
  if HasIdentity then
    Result :=  'Set Identity_Insert ' +  tableName +  ' ON' + #13#10
               + Result
               + 'Set Identity_Insert ' +  tableName +  ' OFF' + #13#10

end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  Memo1.Text := GetTableValueScript(ADS,'[Adressen]',true);
end;