What is the fastest way to push data to SQL Server

[bernr]

What is the fastest way to push data to SQL Server?
I have about 500 meg of data I need to load into our database. Right now I am using TCRBatchMove to move the data and I am currently on hour 3 of the data move and it is only 50% done.

Our customers have to load the data into SQLServer once a quarter for our application, and having it take this long will not fly.

Suggestions?

[Challenger]

Try to use the TMSLoader component.

[bernr]

I actually found out about that last night, but now here is the problem I am having,
Some tables are loading fine
Some are not giving errors but the table in SQL is empty
Some are giving the error "Invalid character value for cast specification"
Some are giving the error" Unspecified error"
While other are giving the error "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

Some of the tables that are giving errors have the same layout as the tables that work. I also have not been able to get a single table with a blob to load properly.

Here is my code I am using to load, I am loading from a DBISAM dataset.

Code: Select all

procedure TForm1.btntestClick(Sender: TObject);
var
  slTables: TStringList;
  sTableName: string;
  I: Integer;
begin
  slTables := TStringList.create;
  DBISAMSession1.GetTableNames(txtDBLocation.text, slTables);

  DBISAMTable.DatabaseName := txtDBLocation.text;
  memStatus.Lines.clear;
  for I := 0 to slTables.count - 1 do
    begin
      sTableName := slTables.strings[i];
      memStatus.Lines.Insert(0, ' Begin ' + sTableName + ' - ' + timetostr(now));
      DBISAMTable.TableName := sTableName;
      DBISAMTable.open;
      MSQuery.SQL.text := 'Delete from ' + sTableName;
      MSQuery.Execute;
      memStatus.Lines.Insert(0, 'Begin Load - ' + timetostr(now));
      cxProgressBar.Position := 0;
      application.ProcessMessages;
      MSLoader.tablename := sTableName;
      try
        MSLoader.LoadFromDataSet(DBISAMTable);
      except
        on E: Exception do
          begin
            memStatus.Lines.Insert(0, ' Error: ' + e.Message);
          end;
      end;
      DBISAMTable.close;
    end;
end;

Any Suggestions?

Using Delphi 2007
SDAC Version 4.35.1.15

[bernr]

I found the problem, the MSLoader was keeping the columns, adding an MSLoader.Columns.clear before the loadfromdataset fixed the problem.


Bern.