Results 1 to 7 of 7

Thread: Insert Multiple Records at once

  1. #1
    Junior Member
    Join Date
    Feb 2007
    Answers
    10

    Insert Multiple Records at once

    Hi,
    This is Radhi.I am using SQL plus .I want to know whether there is any way to insert into the table a set of records from a text editor
    Regards,
    Radhi.


  2. #2
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    Re: Insert Multiple Records at once

    Hi Radhi,

    Provide the insert statement with the values in the text editor as u do in the SQL prompt.

    1. Open the editor
    SQL> ed ins.sql
    This will open a file named ins.sql or else u can open the buffer (afiedt.buf) using just ed command.

    2. write the Query
    INSERT INTO emp(empno, empname, sal) VALUES (1,'RADHI',25000);
    save the file & exit

    3. Execute
    SQL> sta ins (or)
    SQL> @ins

    The above can be used to execute a query in a file.
    Think this what u want, if not feel free to ask :-)

    *** Mangai Varma ***


  3. #3
    Junior Member
    Join Date
    Feb 2007
    Answers
    10

    Re: Insert Multiple Records at once

    Hi Mangai Varma,

    I think I am not clear about asking what I wanted,sorry for that.

    I had a lot of records I mean the insert statements to insert which are actually present in a text file.I just wanted to know if i had any way to insert the whole at once instead of inserting one by one.


    example:
    NSERT INTO CT_EMPLOYEE VALUES
    (7369, 'Debbie Howe', 'CLERK', 7902,
    TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
    INSERT INTO CT_EMPLOYEE VALUES
    (7499, 'Tom Wilkins', 'SALESPERSON', 7698,
    TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
    INSERT INTO CT_EMPLOYEE VALUES
    (7521, 'Ken Burton', 'SALESPERSON', 7698,
    TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
    INSERT INTO CT_EMPLOYEE VALUES
    (7566, 'Christine Turner', 'MANAGER', 7839,
    TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
    INSERT INTO CT_EMPLOYEE VALUES
    (7654, 'Don Allen', 'SALESPERSON', 7698,
    TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
    INSERT INTO CT_EMPLOYEE VALUES
    (7698, 'Chris Donaldson', 'MANAGER', 7839,
    TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
    INSERT INTO CT_EMPLOYEE VALUES
    (7782, 'Ronald Billing', 'MANAGER', 7839,
    TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
    INSERT INTO CT_EMPLOYEE VALUES
    (7788, 'Diana Frye', 'ANALYST', 7566,
    TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
    INSERT INTO CT_EMPLOYEE VALUES
    (7839, 'Larry Williams', 'PRESIDENT', NULL,
    TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
    INSERT INTO CT_EMPLOYEE VALUES
    (7844, 'Susan Ward', 'SALESPERSON', 7698,
    TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
    INSERT INTO CT_EMPLOYEE VALUES
    (7876, 'David Johnson', 'CLERK', 7788,
    TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
    INSERT INTO CT_EMPLOYEE VALUES
    (7900, 'Jerry Smith', 'CLERK', 7698,
    TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
    INSERT INTO CT_EMPLOYEE VALUES
    (7902, 'Ed Young', 'ANALYST', 7566,
    TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
    INSERT INTO CT_EMPLOYEE VALUES
    (7934, 'Mike Womark', 'CLERK', 7782,
    TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
    INSERT INTO CT_EMPLOYEE VALUES
    (7950, 'Nancy Jones', 'ANALYST', NULL,
    TO_DATE('10-APR-1989', 'DD-MON-YYYY'), 3200, NULL, 10);
    CREATE TABLE CT_EMPLOYEE_BACKUP AS SELECT * FROM CT_EMPLOYEE;

    Thanks for taking time ,
    Radhi.


  4. #4
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    Re: Insert Multiple Records at once

    Hi Radhi,
    Your question was misleading.
    Thats ok, no probs, but kindly phrase ur question clearly & in detail when required.

    Now coming to your actual problem, this can be achieved by using substitution variables like Single ampersand (&), Double ampersand (&&) & DEFINE command.
    In your case, u have to use single ampersand (&).

    SQL> insert into emp_new(empno, ename, sal) values(&empno, '&ename', &sal);
    Enter value for empno: 123
    Enter value for ename: RADHI
    Enter value for sal: 25000
    old 1: insert into emp_new(empno, ename, sal) values(&empno, '&ename', &sal)
    new 1: insert into emp_new(empno, ename, sal) values(123, 'RADHI', 25000)

    1 row created.

    SQL> /
    Enter value for empno: 125
    Enter value for ename: EASWAR
    Enter value for sal: 25000
    old 1: insert into emp_new(empno, ename, sal) values(&empno, '&ename', &sal)
    new 1: insert into emp_new(empno, ename, sal) values(125, 'EASWAR', 25000)

    1 row created.

    Hopefully i have cleared your doubt :-)

    *** Mangai Varma ***


  5. #5
    Junior Member
    Join Date
    Feb 2007
    Answers
    10

    Re: Insert Multiple Records at once

    Ya its workin,thanks


  6. #6
    Junior Member
    Join Date
    Feb 2007
    Answers
    10

    Re: Insert Multiple Records at once

    Hi,
    One more doubt
    Can u explain in detail about DEFINE?

    Regards
    Radhi.


  7. #7
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    Re: Insert Multiple Records at once

    Quote Originally Posted by Radhi09 View Post
    Hi,
    One more doubt
    Can u explain in detail about DEFINE?

    Regards
    Radhi.

    Hi Radhi,
    DEFINE command is used to predefine variables using SQL*Plus.

    Syntax:
    DEFINE variable = value
    creates the user variable & assigns the value.

    Example:
    DEFINE empno = 130

    One more thing Radhi, if u find a post satisfying, then click "Thanks" button available in the right corner of the particular post u want to thank.

    *** Mangai Varma ***


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact