Copy fields from one table to another

[SitesMasstec]

Hello colleagues!

I have 2 tables: Table A has 30 fields and Table B has 34 fields.

The 30 first fields in table B are the same (names) in table A. So, Table B has 4 additional fields.

How can I select and copy some filtered records from Table A to Table B, without excluding the last 4 fields in Table B?

I used the command bellow, the records were correctly copied to Table B, but the structure in Table B became with the same structure in table A, that is, the last 4 fields in Table B were lost...

SELECT * FROM TableA.DBF WHERE LOCALIZA=YLOCALIZA ORDER BY CHAVEPAX INTO TABLE TableB.DBF

Thank you,

 

[Joe Crescenzi]

Since there are only 4 fields, the simplest thing would be to just put four placeholder values at the end.

Just be sure that the placeholders are the same type and size as the ones you need to match the other table.

For example:

select tableA.*;
    ,space(25) as Missing1;
    ,space(10) as Missing2,
    ,cast(0.00 as N(10,2) as Missing3;
    ,{} as Missing4;
WHERE LOCALIZA=YLOCALIZA ;
ORDER BY CHAVEPAX ;
   INTO TABLE TableB.DBF

This will give you all the columns from TableA, plus the 4 missing columns with placeholders with the correct types and field sizes.

 

[Doug Lindauer]

If you do that you'll overwrite TableB with the structure from TableA and the contents will only be that of the select statement.

You could either do an "append from" operation where you first copy the rows out of TableA into a temporary file and then append from the temporary file
or
Do a sql insert and select statement like this
INSERT INTO TABLEB( field list ) SELECT( matching field list ) FROM TABLEA WHERE LOCALIZA=YLOCALIZA

When I tried this I found that I couldn't make it work unless both tables were in the default folder but it works.
Also I don't understand what you mean by "without excluding the last 4 fields in Table B". TableA doesn't have information for those last 4 fields so they're going to be blank.

 

[SitesMasstec]

Joe and Doug: I am going to try your solutions now.

Table B, with all 34 fields, is already in the Data Environment of a Report, and there is a linking between a field in Table B and another table (let's say Table C). So I cannot generate Table B from Table A.

Thank you.

 

[SitesMasstec]

First, I did this:

select * FROM PASSAGEIROS.DBF;
    ,cast(0.00 as N(12,2)) as Missing1;
    ,cast(0.00 as N(12,2)) as Missing2;
    ,cast(0.00 as N(12,2)) as Missing3;
    ,cast(0.00 as N(12,2)) as Missing4;
WHERE LOCALIZA=YLOCALIZA ;
ORDER BY CHAVEPAX ;
   INTO TABLE TEMPPAX.DBF

And an error appeared in the first line (select * FROM PASSAGEIROS.DBF): Not a character expression

 

[Doug Lindauer]

You have to put the "from" AFTER the field list as in :
select *, ;
, ;
, from PASSAGEIROS where ...

Note that doing this creates a new table TEMPPAX so if that's what you want, OK. It overwrites any existing TEMPPAX file.
Also if you want to append the selected records into an existing TEMPPAX you don't need to create placeholders for fields that aren't in PASSAGEIROS. If you use "append from" it will match by field name and suck in the records correctly.

Also it would be useful to include more clear details of what you're actually trying to do. You CAN get too long with descriptions but people almost always never include enough details.

 

[SitesMasstec]

Ok, Doug, now it works, BUT only the first record appeared in the report (it should show 5 records in the Detail band).

 

[Doug Lindauer]

Perhaps you're missing a key field in the detail table. Without seeing everything it's hard to know. Or maybe you have some filter on in the detail table?

 

[Joe Crescenzi]

Sorry, that code I wrote was more like pseudocode for an example of how to add some extra columns.

Yes, you need a FROM clause, and yes, it will erase the target table, so if you want this to append rows into a target, you don't need to do anything to account for the extra columns, just insert into the second table as Doug pointed out. It doesn't matter if the target has more columns.

 

[EinTerraner]

I used the command bellow, the records were correctly copied to Table B, but the structure in Table B became with the same structure in table A, that is, the last 4 fields in Table B were lost...

SELECT * FROM TableA.DBF WHERE LOCALIZA=YLOCALIZA ORDER BY CHAVEPAX INTO TABLE TableB.DBF

with this "SELECT FROM ... INTO TABLE" You'll create a new Table.
You can use

SELECT TableB
APPEND FROM TableA FOR LOCALIZA=YLOCALIZA

alterative You can use the SCATTER / GATHER commands

 

[Doug Lindauer]

Ok, Doug, now it works, BUT only the first record appeared in the report (it should show 5 records in the Detail band).

Did you ever figure out the problem?

BTW EinTerraner is certainly right that "append from ... for " is a solution. It's the simplest.

 

[ggreen61]

What if you select the two tables separately in a UNION clause? Use place holders in the missing fields for the first table.

 

[SitesMasstec]

Yes, I did what EinTerraner advised, and as Doug said, it is the simplest.

USE TEMPPAX EXCLUSIVE 
DELETE ALL
PACK
USE

USE TEMPPAX 
APPEND FROM PASSAGEIROS FOR LOCALIZA=YLOCALIZA
USE

The file TEMPPAX has now 2 records after a filter is applied (it can have maximum 5 records):


Now, problem is, The Report shows only the first Detail record:



This is the report design:


What am I missing?

Thank you.

 

[EinTerraner]

What am I missing?

mby a "GO TOP IN TEMPPAX" before you run the report

 

[SitesMasstec]

Ein, it doesn't work, either (USE TEMPPAX.... GOTO TOP), before running the report.

 

[EinTerraner]

as much as i see here you have only the plain fieldnames in the report.
change them to TEMPPAX.fieldname (i.e. TEMPPAX.tarimarbr)

 

[SitesMasstec]

Yes, Ein, all the fields in the Detail2 of the Report have TEMPPAX. in front of the field name:

 

[EinTerraner]

Code:

USE TEMPPAX EXCLUSIVE
DELETE ALL
PACK
USE

USE TEMPPAX
APPEND FROM PASSAGEIROS FOR LOCALIZA=YLOCALIZA
USE

just saw it now... You're closing the TEMPPAX right after the append.

i hope so You open the TEMPPAX before run the report and make sure it's selected like

USE TEMPPAX
SELECT TEMPPAX
REPORT FORM.....

is there any code in the beginning of the report?

 

[Chriss Miller]

The main report driving workarea is driving the main detail band, detail1, why do you have a detail2 band? Why is everything in detail2? You act against the simplest report principle. A report mainly is about the main inital detail band.

 

[SitesMasstec]

This is the code, Ein:

USE TEMPPAX EXCLUSIVE 
DELETE ALL
PACK
USE

USE TEMPPAX 
APPEND FROM PASSAGEIROS FOR LOCALIZA=YLOCALIZA
USE

YWTOTMARBR = 0.00
YWTOTMARUS = 0.00
YWTOTAERBR = 0.00
YWTOTAERUS = 0.00

USE TEMPPAX
GOTO TOP

DO WHILE NOT EOF()
    * Total Maritimo BR do Passageiro
    YWTOTMARBR = tarimarbr + tpormarbr + tranmarbr + servmarbr
    REPLACE WTOTMARBR WITH YWTOTMARBR

    * Total Maritimo US do Passageiro
    YWTOTMARUS = tarimarus + tpormarus + tranmarus + servmarus
    REPLACE WTOTMARUS WITH YWTOTMARUS

    * Total Aéreo BR do Passageiro
    YWTOTAERBR = tariaerbr + tembaerbr
    REPLACE WTOTAERBR WITH YWTOTAERBR

    * Total Aéreo US do Passageiro
    YWTOTAERUS = tariaerus + tembaerus
    REPLACE WTOTAERUS WITH YWTOTAERUS
    
    SKIP
ENDDO
USE

* ------------------------------------------------------------------------------------
USE TEMPPAX
SELECT TEMPPAX

SET REPORTBEHAVIOR 90     && Necessário para mostrar termo MARÍTIMO rotacionado a 270 graus
REPORT FORM RELATRESERVAS7 FOR LOCALIZA=YLOCALIZA TO PRINTER PROMPT PREVIEW    && era RELATRESERVAS4, sem as linhas acima
SET REPORTBEHAVIOR 80
thisform.release()
RETURN