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.