Insert Multiple Records at once

[Radhi09]

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.

[Innila]

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 ***

[Radhi09]

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.

[Innila]

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 ***

[Radhi09]

Ya its workin,thanks

[Radhi09]

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

Regards
Radhi.

[Innila]

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 ***