Re: how to do a CPYF in SQL?

INSERT INTO CA1665AFTT.EMODESADV3
(E3nano, E3cprd, E3cqty)
SELECT suno, prdc, qqty
FROM monica.emod

this gives error Column QQTY not in specified tables. this
is what is needed to copy column to column

On Wed, Jan 6, 2016 at 10:50 AM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

that is good. but I now have to copy specific columns but they have
different names. I need something like this: column to column is this
possible?

INSERT INTO CA1665AFTT.EMODESADV3 (E3nano, E3cprd, E3cqty) FROM
Monica.emod values (suno, prdc, qqty)

On Wed, Jan 6, 2016 at 10:41 AM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

note that unlike CPYF, the column names don't have to be the same when
using SQL.

For that matter, the attributes don't have to be the same either. They
just have to be compatible. For instance, you can use the given SQL when
COLUMN1 is CHAR(10) in table 1 but CHAR(20) in table 2.

Generally speaking, production code shouldn't use SELECT * FROM TBL or the
equivalent INSERT INTO TABLE. Explicitly naming the columns is the best
practice as in the OP's post. This allows tables 1 or table 2 to have
columns added without breaking the code.

The possible exception that proves the rule, is when you absolutely must
have an exact duplicate for a work file.

However, there are better solutions in SQL for creating exact
duplicates..consider

CREATE TABLE TABLE2 LIKE TABLE1 WITH DATA;

DECLARE GLOBAL TEMPORARY TABLE TABLE2 LIKE TABLE1 WITH DATA WITH REPLACE;

Charles







On Wed, Jan 6, 2016 at 10:19 AM, Jeff Young <jyoung0950@xxxxxxxxx> wrote:

If table 1 and table 2 have the same field names and attributes, you can
use:
insert into table 2 select * from table 1 (where .....)

Jeff Young
Sr. Programmer Analyst

On Wed, Jan 6, 2016 at 10:05 AM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

HI I would like to basically an CPYF, but in sql. This would work to

create

a copy of a file with adding all columns and data. But what I want is

the

table2 already exists, can i use this below example to copy all

contents

data into table2 from table1?


INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.