File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes INSERT INTO SELECT FROM Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "INSERT INTO SELECT FROM" Watch "INSERT INTO SELECT FROM" New topic
Author

INSERT INTO SELECT FROM

Russell Peters
Ranch Hand

Joined: Jan 25, 2006
Posts: 50
I have a table with about 50 columns in it. I want to copy a row and insert it in the same table. Of course I need to change the primary key before inserting. How can I do this so i dont have to type all the 50 columns names?
This is what I have so far:


where Col1 is the primary key. Is there a way i can use the * construct and still be able to change the primary key value?


Thanks
[ October 16, 2006: Message edited by: Russell Peters ]

SCJP 1.4
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

How about if you add an extra row to the copied to table. So you can copy all 50 columns, and the 51st is the new tables actual PK.

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Russell Peters
Ranch Hand

Joined: Jan 25, 2006
Posts: 50
Originally posted by Mark Spritzler:
How about if you add an extra row to the copied to table. So you can copy all 50 columns, and the 51st is the new tables actual PK.

Mark


I am sorry, I don't quite understand what you are saying. The 'copied from' and the 'copied to' tables are the same. I want to add the entire row again except that I change the primary key. What I have already, works. I was just wondering if there was a nicer way to do this without having to type in all 50 column names in the SQL.

Thanks
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

I thought you were creating a record with the column names.

I can't think of anyway to do that because you have to change the primary key.

Mark
Russell Peters
Ranch Hand

Joined: Jan 25, 2006
Posts: 50
Originally posted by Mark Spritzler:
I thought you were creating a record with the column names.

I can't think of anyway to do that because you have to change the primary key.

Mark


yeah, thanks however, i guess i was just being lazy
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

Originally posted by Russell Peters:


yeah, thanks however, i guess i was just being lazy


Lazy programmers are usually the best programmers.

Mark
James Ruddy
Greenhorn

Joined: Sep 26, 2007
Posts: 1
Originally posted by Russell Peters:
I have a table with about 50 columns in it. I want to copy a row and insert it in the same table. Of course I need to change the primary key before inserting. How can I do this so i dont have to type all the 50 columns names?
This is what I have so far:


where Col1 is the primary key. Is there a way i can use the * construct and still be able to change the primary key value?


Thanks

[ October 16, 2006: Message edited by: Russell Peters ]




For those who are interested - one way to easily do this is to use a dummy table.

CREATE TABLE MC.MYTRANS_TEMP AS (SELECT * FROM mc.mytrans where myoi = 1111111);

update mc.mytrans_temp set myoi = 222222

insert into mc.mytrans (select * from mc.mytrans_temp where myoi = 222222)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: INSERT INTO SELECT FROM