How to copy single record?

[daniele]

Hi,
i've 2 tables into 2 distinct alias.

I need to copy a single record from table A into table B, what is the fastest way?

The two tables have the same structure.


My example:

      while not TableA.Eof do
      begin
          if not TableB.Locate('CUSTOMER_ID', TableA['CUSTOMER'].AsString+ IntToStr(TableA['ID'].AsINTEGER), []) then
          begin
               //COPY current record of TableA into TableB
          end;

          TableA.Next;
      end;

There is a way to copy all fields value?

var i: Integer;
Fld: TField;

for i := 0 to TableA.FieldCount - 1 do begin
Fld := TableB.FindField(TableA.Fields[i].FullName);
if Fld <> Nil then
Fld.Value := TableA.Fields[i].Value;
end;

On 29.06.2012 1:06 PM, daniele wrote:
> Hi,
> i've 2 tables into 2 distinct alias.
>
> I need to copy a single record from table A into table B, what is the
> fastest way?
>
> The two tables have the same structure.
>
>
> My example:
>
>
> Code:
> --------------------
>
> while not TableA.Eof do
> begin
> if not TableB.Locate('CUSTOMER_ID', TableA['CUSTOMER'].AsString+ IntToStr(TableA['ID'].AsINTEGER), []) then
> begin
> //COPY current record of TableA into TableB
> end;
>
> TableA.Next;
> end;
>
> --------------------
>
>
> There is a way to copy all fields value?
>
>

[daniele]

Thank you Wolfgang,
do you think that is the best way?

I've used TableB.CopyFields(TableA) but is very slow.

There is a way to speedup using a sql?

My table is like this:

TABLE A:
ID (Autoinc)
CUSTOMER_ID (SHORTSTRING)
Other field

I have more copy of TableA, one for each customer, and I want that all records of any TableA is copied into a TableB.

Can i do it by a sql?

Hi (Ciao),


insert into TableB
select * from TableA [where....]


Alessandro Romano

"daniele" ha scritto nel messaggio
news:daniele.5exc1i@nx-forums.nexusdb.com...


Thank you Wolfgang,
do you think that is the best way?

I've used TableB.CopyFields(TableA) but is very slow.

There is a way to speedup using a sql?

My table is like this:

TABLE A:
ID (Autoinc)
CUSTOMER_ID (SHORTSTRING)
Other field

I have more copy of TableA, one for each customer, and I want that all
records of any TableA is copied into a TableB.

Can i do it by a sql?


--
daniele

If both tables are in same nxServer, you can use:

INSERT INTO ALIASB.TABLEB (Field1, Field2, Field3.. FieldN)
(SELECT Field1, Field2, Field3.. FieldN FROM ALIASA.TABLEA WHERE ...)




Em 29/06/2012 08:06, daniele escreveu:
> Hi,
> i've 2 tables into 2 distinct alias.
>
> I need to copy a single record from table A into table B, what is the
> fastest way?
>
> The two tables have the same structure.
>
>
> My example:
>
>
> Code:
> --------------------
>
> while not TableA.Eof do
> begin
> if not TableB.Locate('CUSTOMER_ID', TableA['CUSTOMER'].AsString+ IntToStr(TableA['ID'].AsINTEGER), []) then
> begin
> //COPY current record of TableA into TableB
> end;
>
> TableA.Next;
> end;
>
> --------------------
>
>
> There is a way to copy all fields value?
>
>

[daniele]

Hi Samuel,
thank you for your reply.

This is my scenario:

I have a copy TableA of every customer in their specific alias,

eg:
TableA (Customer 001)
ID | Customer | Other fields...
1 001
2 001

TableA (Customer 002)
ID | Customer | Other fields...
1 002
2 002
3 002

I need a sql that insert all records of previous table, into TableB like this:

TableB (Customer 001 + Customer 002)
ID | Customer | Other fields...
1 001
2 001
3 002
4 002
5 002

Ok, this is easy, tomorrow i will have:

TableA (Customer 001)
ID | Customer | Other fields...
1 001
2 001
3 001 <-- new record
4 001 <-- new record

TableA (Customer 002)
ID | Customer | Other fields...
1 002
2 002
3 002
4 002 <-- new record
5 002 <-- new record
6 002 <-- new record

The new TableB must be:

TableB
ID | Customer | Other fields...
1 001
2 001
3 002
4 002
5 002
6 001 <-- new record from TableA Customer 001
7 001 <-- new record from TableA Customer 001
8 002 <-- new record from TableB Customer 002
9 002 <-- new record from TableB Customer 002
10 002 <-- new record from TableB Customer 002
11 002 <-- new record from TableB Customer 002


Can you help me?
Can i do it by a query?
What is the quickest way?

Thank you,
regards
Daniele

Hi Daniele,

The best way, if possible, is use only one SQL because the data does not
"travel" over the network (everything is handled on the server).

Assuming that the ID field is the primary key, you could do the following:

INSERT INTO ALIASB.TABLEB (ID, CUSTOMER, OTHER FIELDS...)
(SELECT ID, CUSTOMER, OTHERFIELDS... FROM ALIASA.TABLEA A WHERE
NOT EXISTS (SELECT 'X' FROM ALIASB.TABLEB B WHERE B.ID = A.ID) )

[]s

Samuel

[daniele]

Thank you Samuel,
Grazie Alessandro.

Samuel, with your query work fine, i've only one problem with AutoInc field of destination table, because if i use

INSERT INTO ALIASB.TABLEB
(SELECT * FROM ALIASA.TABLEA A WHERE
NOT EXISTS (SELECT 'X' FROM ALIASB.TABLEB B WHERE B.ID = A.ID) )

the query copy Autoinc values and then, in the destination table, i've duplicate values of Autoinc field.

There is a way to copy all fields, without AutoInc field (and without list all fields) ?

Thank you
regards
Daniele

Something like:

INSERT INTO ALIASB.TABLEB (field1, field2 ... field 99)
(SELECT field 1, field 2 etc etc FROM ALIASA.TABLEA A WHERE
NOT EXISTS (SELECT 'X' FROM ALIASB.TABLEB B WHERE B.ID = A.ID) )

Should do it I think.

Dave

[daniele]

Thanks Dave,
I do not want to list all the fields one at a time, if it's possible....