I have to send a table data as insert script to another person. How to create insert script for table data in SQL * PLUS?
I have to send a table data as insert script to another person. How to create insert script for table data in SQL * PLUS?
ya you can send it by simply write the query in the text editor, in the text editor first crete a table if table is not exist and written a insert queries to it when he will run that script it is created a table and after that script will insert the data in it.
pls do it so that you will be clear ....
thanks and regards
In toad we can simply create it by right clicking on the grid and if we select create insert for all rows it will automatically create insert script.
In SQL *Plus if there are 20,000 records do I need to write insert command 20000 times in text editor to create script? Is there any other way to create the script using direct command or procedure?
I dont know if we could create multiple insert statements like in TOAD, But you can try spooling the data to be inserted into a text file and then use BULK_INSERT to insert data from that file into the table.
Example for BULK_INSERT is given below:
BULK INSERT SalesOrderDetail
FROM 'f:\orders\lineitem.txt'
WITH
(
FIELDTERMINATOR ='|',
ROWTERMINATOR ='\n'
)
where SalesOrderDetail = table name, 'f:\orders\lineitem.txt' = file from which the data is to be inserted.
Try this
Sql > set heading off
Sql > select 'insert into emp values('||empno||','||ename||''||job||','||sal||','||comm||','||deptno||','||mgr||','||hiredate||');' from emp;
Sql > Spool insert_s.sql;
Sql > /
Sql > Spool off;
Using this u can find insert script of emp .To run the script use the below statement
Sql >@ insert_s.sql;