16 October, 2012

Oracle Script Generate for insert data into table



Use this following format/query for generate script for insert data into database (Table) .When you export data or migrate data from one database to other database it may help you.With out creating the dumps you can export data from one database to other database.But it is table wise.

You can simply generate script for insert  and then run the generated script on command line.

Example 1 :-

 SELECT 'INSERT INTO DUAL VALUES ('''||dummy||''');' FROM DUAL;

output :
INSERT INTO DUAL VALUES ('X');

Example 2 :-

SELECT 'INSERT INTO EMP_DETAILS VALUES (',''''||EMP_NAME||'''',',',''''||EMP_SEX||'''',',',''''||EMP_JOIN_DT||'''',');' FROM M_EMPLOYEE;


output:
INSERT INTO EMP_DETAILS VALUES (    'Rakesh'  ,  'M'  ,  '03-APR-05'    );
INSERT INTO EMP_DETAILS VALUES (    'Manoj Kumar' ,   'M' ,   '06-APR-05'    );
INSERT INTO EMP_DETAILS VALUES (    'Santosh Kumar'  ,  'M'  ,  '02-JAN-05'    );
INSERT INTO EMP_DETAILS VALUES (    'Rakesh Kumar'  ,  'M'  ,  '05-JAN-05'    );
INSERT INTO EMP_DETAILS VALUES (    'Sunil Dev'  ,  'M'  ,  '01-APR-05'    );
INSERT INTO EMP_DETAILS VALUES (    'Sheeba'  ,  'F'  ,  '01-JAN-05'    );



Use the generated output in command line and execute .