Home » SQL & PL/SQL » SQL & PL/SQL » sql query for insertion
sql query for insertion [message #304063] Mon, 03 March 2008 23:09 Go to next message
jeer
Messages: 37
Registered: February 2008
Location: pakistan
Member
Hi

I have two tables Book_Heading1 and Book_heading2 Book_heading1 has 2 fields:
ddc Number
Heading Varchar2(4000)

and Book_heading2 has 3 fields:
id Number
ddc Number
Heading Varchar2(4000)

Book_heading1 has 45000 records and I want to insert these records into Book_heading2 using following query:

INSERT INTO Book_heading2 (ddc,Heading) SELECT ddc,Heading from Book_heading1

in Book_heading2 column "id" is defined as NOT NULL and also work as primary key. Please guide me to write a query that will also insert into "id" column i.e 1,2,3,4,......45000.


thanks
Re: sql query for insertion [message #304070 is a reply to message #304063] Mon, 03 March 2008 23:30 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
create sequence for Book_heading2 column "id" and put this in insert statement.as id is primary key,so you can't insert value without it.

regards,

Re: sql query for insertion [message #304072 is a reply to message #304063] Mon, 03 March 2008 23:39 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,

simply use ROWNUM pseudocolumn.
SELECT ROWNUM id, ddc, Heading
FROM Book_heading1;
Be aware, the ID's will be assigned without any relationship to source data (eg. greater DDC may have greater or smaller ID); the IDs will be only unique.
Re: sql query for insertion [message #304089 is a reply to message #304063] Tue, 04 March 2008 00:30 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
due to rownum puzzle,i prefered sequence.

regards,
Re: sql query for insertion [message #304091 is a reply to message #304063] Tue, 04 March 2008 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=32205&SkipA=0#110632

@mshrkshl, which puzzle with rownum?

Regards
Michel
Re: sql query for insertion [message #304106 is a reply to message #304063] Tue, 04 March 2008 01:16 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
one case is multiple insert operation .

regards,
Re: sql query for insertion [message #304109 is a reply to message #304106] Tue, 04 March 2008 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe but this is another question, doesn't it?

Regards
Michel
Re: sql query for insertion [message #304115 is a reply to message #304063] Tue, 04 March 2008 02:09 Go to previous message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
yes,you are right michel.

thanks and regards,
Previous Topic: SQL query for collection load
Next Topic: How I retrive the any users table satistics during dml operetion from sys user
Goto Forum:
  


Current Time: Mon Dec 05 03:06:28 CST 2016

Total time taken to generate the page: 0.10884 seconds