Solved

extract data from a table into a file

Posted on 2002-03-23
7
498 Views
Last Modified: 2006-11-17
I like to extract 2 fields f1 and f2 from a Sybase table CMC_GRGR, into a fileA, then use the fileA to creat a NEW table in Oracle to contain f1 and f2.  How can I do that?

Thank you in advance.
0
Comment
Question by:white_bird
  • 3
  • 2
  • 2
7 Comments
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6894174
Step 1: Create a view in Sybase as
Create view CMC_GRGR_View as
select f1, f2 from CMC_GRGR

Step 2: Extract the view into a flat file using BCP

Use bcp -h to see more option.

Cheers,
Amit
0
 

Author Comment

by:white_bird
ID: 6895434
I created the view.  I do not know how to use the bcp utility.  Can you help me more.
Thank you.
white-bird
0
 

Author Comment

by:white_bird
ID: 6895435
I created the view.  I do not know how to use the bcp utility.  Can you help me more.
Thank you.
white-bird
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 5

Accepted Solution

by:
amitpagarwal earned 100 total points
ID: 6895446
At the prompt say:

bcp database..CMC_GRGR_View  out flatfilename -c -U username -P password -S server

Cheers !!

usage: bcp [[database_name.]owner.]table_name[:slice_number] {in | out} datafile
        [-m maxerrors] [-f formatfile] [-e errfile]
        [-F firstrow] [-L lastrow] [-b batchsize]
        [-n] [-c] [-t field_terminator] [-r row_terminator]
        [-U username] [-P password] [-I interfaces_file] [-S server]
        [-a display_charset] [-q datafile_charset] [-z language] [-v]
        [-A packet size] [-J client character set]
        [-T text or image size] [-E] [-g id_start_value] [-N] [-X]
        [-M LabelName LabelValue] [-labeled]
        [-K keytab_file] [-R remote_server_principal]
        [-V [security_options]] [-Z security_mechanism]
0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6895447
substitue database, server, user, password with ur specific values
0
 

Expert Comment

by:Ejaz
ID: 6939926
Hi,
   After creating the View, just use this following bcp command at the operating system prompt.

bcp <DB NAME>..<TBL NAME> out -U <User NAME> -P <Password> -t "|" -c -S <Server Name>


U can use any character as seprator between the value of 2 filed , just as I had used "|"

After that use the text file which is the o/p of above for SQL Loader to load the data into Oracle Database. Beofre that U will had to create a table with the same struc. in Oracle and then use SQL Loader.

If any more query let me know.

Till then
Cheers

0
 

Expert Comment

by:Ejaz
ID: 6939927
Hi,
   After creating the View, just use this following bcp command at the operating system prompt.

bcp <DB NAME>..<TBL NAME> out<text file name> -U <User NAME> -P <Password> -t "|" -c -S <Server Name>


U can use any character as seprator between the value of 2 filed , just as I had used "|"

After that use the text file which is the o/p of above for SQL Loader to load the data into Oracle Database. Beofre that U will had to create a table with the same struc. in Oracle and then use SQL Loader.

If any more query let me know.

Till then
Cheers

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with SQL query 3 2,201
Query Taking long time 7 470
Problems writing binary files to Sybase from PHP 2 178
InterSystems Caché OPEN QUERY 4 444
With more and more people tethered to the internet, advertisers can reach you in new and creative ways. Push notifications on your smartphone, customized advertisements based on your previous searches, and email inboxes filled with promotional pitch…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now