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.
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.
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;
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; /
Joined: Jul 12, 2007
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.
Joined: Oct 06, 2007
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
Joined: Jul 12, 2007
Thanks a lot guys. I combined the 2 and I am very happy with the results.