Spool command in SQL :(To copy query result to local file)

Hi everyone,

Sometimes you need to export  SQL query results  to CSV file.You can’t use traditional  copy and paste method for these huge records.So SPOOLING is to your rescue.

First things first!

What is SPOOL?

Spool is a command in SQL plus to Store query results in a file, or optionally sends the file to a printer.

How to use ?

Spooling can be used in SQL*PLUS database.Example for the same is shared below.In SQL*PLUS you can directly use the spool command.

1.Create a table named “product”.

CREATE TABLE product(p_name varchar2(10),p_cost number(3,2));

2.Add products in product table.

INSERT INTO product VALUES (‘Laptop’,1234);

INSERT INTO product VALUES (‘Mobile’,5678);

So now our table contains two records .And you want these records to be copied to a file “products.txt”.

3.This command will copy the output of your queries till you hit command SPOOL OFF.

SPOOL C:\products.txt

4.Hit a query .

SELECT * from product.

5.And you will get output on your products.txt file.

6.When you want to  stop copying output just use..

SPOOL OFF

7.And if you want to just print this spooled data then use below command

SPOOL OUT

This is how you can spool your query results to an existing file.If you want to create a directory through SQL prompt only then this option is also available.

1.To create a new directory for spooling with default file extension.

SPOOL product CREATE 

2.To append on that same file

SPOOL product APPEND

3.To replace the previous data from that file

SPOOL product REPLACE

This is all about SPOOLING in SQL*PLUS.

Spooling command is not available in MySQL .But there are other options  to perform this task.The only difference is in MySQL you have to take  helluva efforts to copy in local file.Rather you can prefer to save the log using tee command.The use of tee command is explained below.

1.tee  /var/log.out

This command will log all the queries and output to your log.out file

Hope you like it !

Do share your feedback IN FORM OF A SQL QUERY!

Ask queries if any!!