Re: Simple (?) insert problem

The different field names are not a problem. Just do:

INSERT INTO pkmstest.afinvn00
SELECT AI00_WAREHOUSE, AI00_LOCATION_TYPE_FLAG, AI00_PHYSICAL_INV_CODE,          AI00_LOCATION, AI00_CASE_NBR
FROM pkmstest.aiinvn00

The values will go into the columns in the order of the destination table's schema.

If your problem is the fact that you are going the other direction (you didn't specify, BTW) and the NOT NULL is worrying you in the destination table, then you will have to add to the select column list a constant to go into that column. If 0 (zero) is the default you want, then do something like:

    SELECT A, B, 0, C If this is not answering your question, then give more detail. Like maybe, what is the problem?

Alex Hudghton wrote:

> I know that I can do :
>
> Insert into <table>
> select <fields> from < other table>
> where .......
>
> However, I have one table like this:
>
> SQL> desc pkmstest.aiinvn00
> Name Null? Type
> ------------------------------- -------- ----
> AI00_WAREHOUSE NOT NULL CHAR(3)
> AI00_LOCATION_TYPE_FLAG NOT NULL CHAR(1)
> AI00_PHYSICAL_INV_CODE NOT NULL CHAR(4)
> AI00_PHYSICAL_COUNT_SEQ_NBR NOT NULL NUMBER(1)
> AI00_LOCATION NOT NULL CHAR(24)
> AI00_CASE_NBR NOT NULL CHAR(20)
>
> and the other like this :
>
> SQL> desc pkmstest.afinvn00
> Name Null? Type
> ------------------------------- -------- ----
> AF00_WAREHOUSE NOT NULL CHAR(3)
> AF00_LOCATION_TYPE_FLAG NOT NULL CHAR(1)
> AF00_PHYSICAL_INV_CODE NOT NULL CHAR(4)
> AF00_LOCATION NOT NULL CHAR(24)
> AF00_CASE_NBR NOT NULL CHAR(20)
>
> As you can see the fields have the same name apart from the prefix of
> the table name. Is there any way round this ??
>
> Regards
>
> Alex
Received on Mon Nov 22 1999 - 12:03:18 CST