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.
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..
7.And if you want to just print this spooled data then use below command
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.
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!!