wood burning stoves 2.0*
The moose likes JDBC and the fly likes Export into file, sql query to insert data Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Export into file, sql query to insert data" Watch "Export into file, sql query to insert data" New topic
Author

Export into file, sql query to insert data

Dylan Margoczi
Ranch Hand

Joined: Jul 12, 2007
Posts: 38
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...
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2476
    
    7

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


OCUP UML fundamental and ITIL foundation
youtube channel
Dylan Margoczi
Ranch Hand

Joined: Jul 12, 2007
Posts: 38
thanks, will give it a try and let you know
Deepak Kumar
Ranch Hand

Joined: Nov 05, 2007
Posts: 62
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.


Thanks,
Deepak
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
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;
/
Dylan Margoczi
Ranch Hand

Joined: Jul 12, 2007
Posts: 38
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.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
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
Dylan Margoczi
Ranch Hand

Joined: Jul 12, 2007
Posts: 38
Thanks a lot guys.
I combined the 2 and I am very happy with the results.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Export into file, sql query to insert data
 
Similar Threads
please help me about EJB-QL......
copy data from one table to another every 30 minutes through a stored procedure in oracle pl sql
how to make insert in to select query lock free
Convertion of xml file to sql file.
Filter data in Java