Echoic Echoic - 5 months ago 7
SQL Question

Create a copy of a table while ommitting and concatenating columns

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.

Answer

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