?
Solved

Dump table, column, row via ODBC via bcp

Posted on 2005-03-22
4
Medium Priority
?
577 Views
Last Modified: 2008-02-01
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
Comment
Question by:Mr_PC2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 2

Expert Comment

by:JackOfAll1
ID: 13614869
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
 

Author Comment

by:Mr_PC2000
ID: 13621917
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
 
LVL 2

Accepted Solution

by:
JackOfAll1 earned 375 total points
ID: 13622063
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
 

Author Comment

by:Mr_PC2000
ID: 13641668

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question