| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: copying data from Oracle database to SQL server
Peter Yu wrote:
> I have data residing in Oracle 7.3 that i wish to distribute to a smaller
> database in Microsoft SQL server. How do i start ?
Peter,
you can do two things:
- use odbc to connect to both databases using a visual basic program
(you'll have to make that yourself) and convert the data with that program.
- you could make a sql statement that makes an ascii dump of the required
table. I'm sure sql server has tools to import ascii files.
to do this you could use this little program in sql*plus:
set heading off
set pause off
set feedback off
set pagesize 9999
set linesize 100 (or anything as long as your longest line)
spool export.asc
select '"'empno||'","'||name||'","'||salary||'","'||deptno||'"'
from emp
/
spool off
This produces a comma delimited file with all values between quotes. If you want to make a fixed length file do this:
set heading off
set pause off
set feedback off
set pagesize 9999
set linesize 100 (or anything as long as your longest line)
spool export.asc
select
rpad(to_char(empno),5)||rpad(name,25)||rpad(to_char(salary),7)||rpad(to_cha
r(deptno),3)
from emp
/
spool off
Using rpad adds spaces to the result, so in the example empno is filled up
to in total 5 position.
Hope this answers your question.
Kind regards
Mike Philippens
--
Posted via CNET Help.com
http://www.help.com/
Received on Fri Oct 29 1999 - 06:30:03 CDT
 |
 |