Link to home
Start Free TrialLog in
Avatar of K Feening
K FeeningFlag for Australia

asked on

Copy rows between Interbase tables

I have 2 interbase tables and I want to copy the data from 1 table to another I am using
Delphi and tQuery
query2.active:=false;
  query2.requestlive:=false;
  query2.sql.clear;
  sql1:='SELECT * FROM Table1';
  query2.sql.add(sql1);
  sql1:='WHERE Termd='+chr(39)+''+chr(39)+'and Empind='+chr(39)+'1'+chr(39);
  query2.sql.add(sql1);
  sql1:='INSERT INTO Table2 (empseq, empno, Namesr, emptype, empname)';
  query2.sql.add(sql1);
  sql1:='Values (:empseq, :empno, :Namesr, :emptype, :empname)';
  query2.sql.add(sql1);
  query2.execsql;

Open in new window


But I am getting error in line 3 Token unknown insert
could you help
ASKER CERTIFIED SOLUTION
Avatar of jimyX
jimyX

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jimyX
jimyX

  query2.sql.clear;
  query2.sql.add('INSERT INTO Table2 (empseq, empno, Namesr, emptype, empname)');
  query2.sql.add('(SELECT * FROM Table1 WHERE Termd='''' and Empind='''1''')');
  query2.execsql;

Open in new window

Copying table data in your Delphi code:
First select the table data in query1 and then write each record one by one to table2 with a separate query...

var
  empseq,empno,Namesr,emptype,empname:string;

Query1.Active:=false;
Query1.SQL.Clear;
Query1.SQL.Add('SELECT * FROM Table1');
Query1.SQL.Add('WHERE Termd='+chr(39)+''+chr(39)+'and Empind='+chr(39)+'1'+chr(39));
Query1.open;
Query1.First;
WHILE not Query1.Eof do begin

  empseq    :=Query1.FieldByName('empseq').Asstring;
  empno     :=Query1.FieldByName('empno').Asstring;
  Namesr    :=Query1.FieldByName('Namesr').Asstring;
  emptype  :=Query1.FieldByName('emptype').Asstring;
  empname:=Query1.FieldByName('empname').Asstring;

  Query2.SQL.Clear;
  Query2.SQL.Add('INSERT INTO Table2 (empseq, empno, Namesr, emptype, empname)');
  Query2.SQL.Add('Values ('''+empseq+''','''+empno+''','''+Namesr+''','''+emptype+''','''+empname+''')');
  Query2.ExeqSql;
  Query1.Next;
end;

Open in new window

both the previous comments will work. I just wanted to add that "select *" is this context is a really bad idea, explicitly name the fields to avoid problems later
My vote goes to jimyXs solution. Let the database engine do this. That is very speedy and
more readable in code. Just make sure that pass exact filter to first table (to select right
records to copy into another)
It's basically correct to let the database engine do the job.

But if you do it in code, you're more flexible in adapting, changing and working on field data if necessary...