• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

Dump table, column, row via ODBC via bcp

I am experimenting with ODBC. I want to pull 1 to 2 tables from a database which is in Oracle SQL Server. I connect into this Oracle database server via ODBC.

Via bcp utility can I? If So any very small exmple to be clear where I must look into? Something says me there is huge incompability between.

Thanks. Mr_P
0
Mr_PC2000
Asked:
Mr_PC2000
  • 2
  • 2
1 Solution
 
JackOfAll1Commented:
I am not sure what you are asking.  

Oracle and SQL Server are two different dbms's.

Oracle's load utility is SQL Loader.

SQL Server uses BCP.

The two load utilities are not compatable.  

If you want to take data from Oracle and load it into SQL Server you can use SQL Loader to extract the data from the table in a tab delimited format and then use BCP to load the data in SQL Server.

You will need to set up the utilities seperately.

Hth



0
 
Mr_PC2000Author Commented:
Hello JackOfAll1. Thank you.

I have an ODBC connection setup already. I can use I guess any query utility via this ODBC successfully and I am currently.

I want to get Table(s) from Oracle and dump them in SQL Server periodically once a week or once a day. That is want I want to do.
0
 
JackOfAll1Commented:
An ODBC connection will allow you or an application to use query the database so you can run commands like
select * from
and get the whole table as a result set.
You could then do a trucate table and an insert to populate the tables in the other database.

It will not allow you to use the utilities to extract and load the data.  The SQL Loader and BCP utilities are much more efficient at unloading and loading data then SQL command. If your tables are going to be small you should be able to get away with plain SQL selects and inserts but if the tables get large you should use the utilities.

Another area to consider is the size of your logs.  Every SQL insert is logged and you can fill the logs if you are not carefull.
0
 
Mr_PC2000Author Commented:

Thank you J.

can I use that ODBC connection via bcp? if yes a small example I am dying for to find out.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now