Home » SQL & PL/SQL » SQL & PL/SQL » Insert Records (9.0.4.0)
Insert Records [message #332549] Tue, 08 July 2008 23:33 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,
I have created a table. It contains columns and data as follows.
Deptno      Job           Sal
---------------------------------------
10             Manager    5000
20             Clerk         3000
30            Analyst      2340
10            Develoer     4300
10            tester         2300
20            sales         1200
30            Executie    2300


I want to insert these data into a table as follows
Deptno        Job          Sal
----------------------------------------
10              Manager    5000
                  Develoer    4300
                  tester        2300 
20              Clerk         3000
                  sales        1200
30              Analyst      2340
                 Executie    2300


so please let me know how to write query for this.(How to insert data as mentioned)

Thank you.
Re: Insert Records [message #332559 is a reply to message #332549] Tue, 08 July 2008 23:58 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I think inserting data like that is a bad idea....

You may want to display like that... then you can use column break on.

By
Vamsi
Re: Insert Records [message #332560 is a reply to message #332549] Wed, 09 July 2008 00:16 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
Yeah thats right.
but anyway you can use the following insert format

insert into <table_name> (p1,p2,p3) values(v1,v2,v3);

for values that are null, you can insert a null by giving ''

to achieve the inserts.
Re: Insert Records [message #332563 is a reply to message #332560] Wed, 09 July 2008 00:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
adit_me1 wrote on Wed, 09 July 2008 07:16
Yeah thats right.
but anyway you can use the following insert format

insert into <table_name> (p1,p2,p3) values(v1,v2,v3);

for values that are null, you can insert a null by giving ''

to achieve the inserts.


Why would you not simply use null for null-values?!
Re: Insert Records [message #332572 is a reply to message #332549] Wed, 09 July 2008 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still unable to use SQL*PLus and proper copy and paste?

What you ask is meaningless.
Do you really want deptno null in some rows? Or do you want them to be DISPLAYED with null?

Regards
Michel
Re: Insert Records [message #332590 is a reply to message #332572] Wed, 09 July 2008 01:57 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Although it doesn't have to mean much regarding the problem, but having in mind that it was user71408 who asked this question, I believe that this might be what he wants:
INSERT INTO this_table
SELECT deptno, job, sal
FROM somewhere
ORDER BY deptno, sal DESC
so that - once you execute a SELECT statement against this table, you'd get records ordered by "deptno, sal desc". I.e. another type of "how do I select records in a specific order without using the ORDER BY clause".

On the other hand, I might be wrong about it.
Re: Insert Records [message #332597 is a reply to message #332590] Wed, 09 July 2008 02:16 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
how do I select records in a specific order without using the ORDER BY clause


not possible I guess... Oracle pics up rows at random if no order by clause is mentioned. consider yourself lucky enough if you get the rows in the order of insertion.
Re: Insert Records [message #332617 is a reply to message #332549] Wed, 09 July 2008 03:32 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
@user71408
Quote:

I want to insert these data into a table as follows


Based on it,



CREATE TABLE TEST_SOURCE_TBL (DEPTNO NUMBER, JOB VARCHAR2(20),SAL NUMBER(8,2));
INSERT INTO TEST_SOURCE_TBL VALUES (10,'Manager',5000); 
INSERT INTO TEST_SOURCE_TBL VALUES (20,'Clerk',3000); 
INSERT INTO TEST_SOURCE_TBL VALUES (30,'Analyst',2340); 
INSERT INTO TEST_SOURCE_TBL VALUES (10,'Develoer',4300); 
INSERT INTO TEST_SOURCE_TBL VALUES (10,'Tester',2300); 
INSERT INTO TEST_SOURCE_TBL VALUES (20,'Sales',1200); 
INSERT INTO TEST_SOURCE_TBL VALUES (30,'Executie',2300); 
COMMIT;

SQL> SELECT * FROM TEST_SOURCE_TBL;

    DEPTNO JOB                         SAL
---------- -------------------- ----------
        10 Manager                    5000
        20 Clerk                      3000
        30 Analyst                    2340
        10 Develoer                   4300
        10 Tester                     2300
        20 Sales                      1200
        30 Executie                   2300

7 rows selected.

SQL> CREATE TABLE  TEST_DEST_TBL AS
  2  SELECT DECODE(Deptno ,LAG(Deptno) OVER(order by Deptno ),null,Deptno )  Deptno,job,  SAL FROM
  3  (SELECT DEPTNO,JOB,SAL FROM TEST_SOURCE_TBL 
  4   GROUP BY Deptno,job,sal ORDER BY SAL DESC)
  5  /

Table created.

SQL> SELECT * FROM TEST_DEST_TBL;

DEPTNO                                   JOB                         SAL
---------------------------------------- -------------------- ----------
10                                       Manager                    5000
                                         Develoer                   4300
                                         Tester                     2300
20                                       Clerk                      3000
                                         Sales                      1200
30                                       Analyst                    2340
                                         Executie                   2300

7 rows selected.


"I think inserting data like that is a bad idea...."-- I am agree with vamsi kasina.

Hope you have got what you expected!
Regards,
Oli

[Updated on: Wed, 09 July 2008 03:33]

Report message to a moderator

Re: Insert Records [message #332632 is a reply to message #332617] Wed, 09 July 2008 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Hope you have got what you expected!

Maybe but this is not what has been asked and you quoted:
Quote:
I want to insert these data into a table as follow

You didn't insert like it but you displayed like it.

Regards
Michel
Re: Insert Records [message #332649 is a reply to message #332632] Wed, 09 July 2008 05:02 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Michel Cadot wrote on Wed, 09 July 2008 04:11
Quote:
Hope you have got what you expected!

Maybe but this is not what has been asked and you quoted:
Quote:
I want to insert these data into a table as follow

You didn't insert like it but you displayed like it.

Regards
Michel




Quote:

I have created a table. It contains columns and data as follows.I want to insert these data into a table as follows.


user71408's query is quite confusing indeed!
"these data" (data that are already in source table??)from source to destination and then display destination table data?

Regards,
Oli

[Updated on: Wed, 09 July 2008 06:29] by Moderator

Report message to a moderator

Re: Insert Records [message #332683 is a reply to message #332649] Wed, 09 July 2008 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"confusing" is not the more correct word but I can't say more. Wink

Regards
Michel
Re: Insert Records [message #332699 is a reply to message #332683] Wed, 09 July 2008 06:56 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Razz yep!
Previous Topic: Is "pipelining" feature available in 10G to select from PL/SQL table?
Next Topic: A SIMPLE QUERY ABOUT ORACLE JOBS
Goto Forum:
  


Current Time: Sat Dec 10 08:53:51 CST 2016

Total time taken to generate the page: 0.05373 seconds