Copy record in the same file.

[Bruce Jin]

I need to use 1 record in a file to create another record in the same file. The new record has the same column values except 1 or 2 columns. Is there a simple way to do this? I try to avoid reading every column to parse a INSERT string?
Thanks.

[Jamie Robertson]

Originally posted by Bruce Jin:
I need to use 1 record in a file to create another record in the same file. The new record has the same column values except 1 or 2 columns. Is there a simple way to do this? I try to avoid reading every column to parse a INSERT string?
Thanks.

The only solution that I can think of ( If I understand your question correctly ) is to use a DB specific SQL statement like:
insert into table2 ( column1, column2,... ) select column1, column2,... from table1 where ....
the above example works for Oracle, but may vary for other DB vendors.
Jamie

[Bruce Jin]

Thanks.
Looks like there is no trick doing this.
I need copy to happen in the same file.

[Jamie Robertson]

Originally posted by Bruce Jin:
Thanks.
Looks like there is no trick doing this.
I need copy to happen in the same file.

why not just change table2 to table1. It will insert into table1 based on a query on the same table( table1 ).

[Bruce Jin]

Thanks again.
I tried and the same table copy works. But now I am duplicating record.
How can I alter 1 of the columns before insert?

I am using this sql string:
insert into cust01 (select * from cust01 where custid=1002)
What I want is to change custid to 1003 before insert the record into database.

[Rudy Dakota]

Hi Bruce,
You 'll need to get rid of the ugly "select *". It 'll have to be something like this

insert into cust01 (col1, ... , coln, custid) (select col1, ... , coln, 1003 from cust01 where custid=1002)

.
Let me also suggest that you 'd do yourself a favour reading up on SQL, if you plan to do this sort of thing more often.
Good riding,
Rudy.

[Bruce Jin]

Thanks Rudy;
Your code works.