I have two tables. The first table is OEPCPHCSP. This table has lots of columns(at least 100 or so).
The second table is OEPCPHOCAS. I want this table to be an exact copy of the first table(OEPCPHCSP) - with a few exceptions.
My first table(OEPCPHCSP) has these four fields:
oecc01, oeyr01, oemo01, and oedy01.
These fields should not be included in the second table(OEPCPHOCAS).
Instead I would like to concatenate those four fields from the first table(OEPCPHCSP) into one field call INVDAT in the second table(OEPCPHOCAS).
I have tried using concat, but did not get very far with that. I'm pretty lost right now as to how to do this. And it doesn't seem like I can copy the table over due to different column numbers.
Any help is appreciated.
I think you want a view that looks like this:
CREATE OR REPLACE VIEW OEPCPHOCAS AS SELECT oecc01 || oeyr01 || oemo01 || oedy01 as INVDAT, field1, .. fieldn FROM OEPCPHCSP
or if you really want a table just just the select part:
INSERT INTO OEPCPHOCAS SELECT oecc01 || oeyr01 || oemo01 || oedy01 as INVDAT, field1, .. fieldn FROM OEPCPHCSP