• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Export into file, sql query to insert data

 
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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...
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Dylan Margoczi
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thanks, will give it a try and let you know
 
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks a lot guys.
I combined the 2 and I am very happy with the results.
 
reply
    Bookmark Topic Watch Topic
  • New Topic