dilshad_ch
asked on
Appending data in a file by sql* plus spool command
Dear all,
here is the scenario
SQL> spool empdata
SQL> select * from emp
...
...
data displayed from emp table
...
...
SQL>spool off
emp data has been stored in the empdata.lst file.
Now i want to append the same file with the dept table data but by the command
SQL> spool emdata
SQL> select * from dept
...
...
...
it washes all the emp data from file and writes the dept data.
My requirement is to write the dept data from the end of the emp data.
thanx in advance.
Dilshad Ch.
here is the scenario
SQL> spool empdata
SQL> select * from emp
...
...
data displayed from emp table
...
...
SQL>spool off
emp data has been stored in the empdata.lst file.
Now i want to append the same file with the dept table data but by the command
SQL> spool emdata
SQL> select * from dept
...
...
...
it washes all the emp data from file and writes the dept data.
My requirement is to write the dept data from the end of the emp data.
thanx in advance.
Dilshad Ch.
ASKER
dear kertzschmar,
the commands you have written does not append data but washes all existing data and then write. i want to append data.
thanks for sparing time
dilshad ch.
the commands you have written does not append data but washes all existing data and then write. i want to append data.
thanks for sparing time
dilshad ch.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
create a file x.sql as
set embedded on;
set linesize 65
spool c:\temp\out.lst
select empno,ename from emp;
select deptno,dname from dept;
spool off;
execute the file from sqlplus
sql>@x.sql
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
14 rows selected.
Elapsed: 00:00:00.60
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
Elapsed: 00:00:00.10
set embedded on;
set linesize 65
spool c:\temp\out.lst
select empno,ename from emp;
select deptno,dname from dept;
spool off;
execute the file from sqlplus
sql>@x.sql
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
14 rows selected.
Elapsed: 00:00:00.60
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
Elapsed: 00:00:00.10
Not sure why other guys missed it. But below solution works perfectly as spool command does have Append option.
SQL> spool empdata
SQL> select * from emp
...
...
data displayed from emp table
...
...
SQL>spool off
emp data has been stored in the empdata.lst file.
SQL> spool emdata append
SQL> select * from dept
...
...
...
SQL>spool off
SQL> spool empdata
SQL> select * from emp
...
...
data displayed from emp table
...
...
SQL>spool off
emp data has been stored in the empdata.lst file.
SQL> spool emdata append
SQL> select * from dept
...
...
...
SQL>spool off
SQL> select * from emp
SQL> select * from dept
SQL> spool Off