Export into file, sql query to insert data

Hi there, forgive me if my wording is not to great here please.

I need to extract data from a database into a file as a SQL statement/query.

I need to do this through my program, is there a statement that i can send to SQL that it can do it for me or would I need to get the data from the database and build the query myself to save???

please help...

An oracle example (untested).this will result into:
insert into MYDESTINATIONTABLE (colA, colB) values ('xxx', 'aaa');
insert into MYDESTINATIONTABLE (colA, colB) values ('yyy', 'bbb');
insert into MYDESTINATIONTABLE (colA, colB) values ('zzz', 'ccc');

Use it only as a tool. Don't give it to end user. There is a risk for sql injection.

Regards, Jan

thanks, will give it a try and let you know

Well I had generated the sql file by writing a java program,this program takes data from MS-Access DB and write the data into a .sql file, this file was then used to insert data into ORACLE.
If you are trying the same purpose, then I would like to give you an idea.

In MySQL, you would use:

SELECT * INTO OUTFILE '/tmp/mycsv.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM mytable;

In Oracle, you would do something like this with pl/sql:

create or replace function dump_csv( p_query in varchar2,
p_separator in varchar2
default ',',
p_dir in varchar2 ,
p_filename in varchar2 )
return number
AUTHID CURRENT_USER
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(2000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' );

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i,
l_columnValue, 2000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;

dbms_sql.define_column( l_theCursor, 1, l_columnValue,
2000 );

l_status := dbms_sql.execute(l_theCursor);

loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i,
l_columnValue );
utl_file.put( l_output, l_separator ||
l_columnValue );
l_separator := p_separator;
end loop;
utl_file.new_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);

utl_file.fclose( l_output );
return l_cnt;
end dump_csv;
/

You would use that for example like this:

create or replace procedure test_dump_csv
as
l_rows number;
begin
l_rows := dump_csv( 'select *
from all_users
where rownum < 25',
',', '/tmp', 'test.dat' );
end;
/

I'm using MySQL.

Paul, I tried what you gave me but that simply gives me a csv type text file, I was hoping to use something as simple as that, but just to create a ".sql" file with the query instead.

Kumar, I'm interested in your idea, I'm doing the same thing, just getting the data from MySQL instead.

thanks.

Combine what Jan suggested with the mySQL outfile (just name it .sql).
In your select, where you would include the SQL commands as literals.

If you need to resolve some formatting issues with the output check the mySQL manual. Search on mySQL+outfile+syntax you should be able to find it pretty quick.

As an alternative, if all your doing is inserting data into a table based on SQL select (which is likely in the same schema)... you really only need a single statement (though this may not fit your criteria):

INSERT INTO myTable (column1, column2, ...)
SELECT column3, column4, ...
FROM myOthertable

Thanks a lot guys.
I combined the 2 and I am very happy with the results.