?
Solved

bcp out/in

Posted on 2003-03-13
12
Medium Priority
?
3,025 Views
Last Modified: 2007-12-19
I want to BCP out a table in to a flat file which is around 1.5GB. I want to bcp out a subset of data each time because of space issue. What are the different approaches to do this. Just keep in mind i can't create views in database. I tried with following command but i am getting syntax error saying syntax near keyword 'where'. database server is sybase 11.9.2
bcp "select * from table where date = 20010101" out  /home/flatfile -Uuser Sserver -c

Could anyone help me.

Thanks,
hmk.
0
Comment
Question by:hmk
[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
  • 4
  • 2
  • 2
  • +2
12 Comments
 
LVL 4

Expert Comment

by:Vinit Kain
ID: 8134959
Hi,
You can not use 'where' in bcp command. If you want to reduce the size of bcp outfile then you can use the script below. The Script will create bcpout file from the table for 1000 rows each.

Vinit...




#!/bin/ksh
integer i
integer j
i=1
TOTAL_NO_OF_ROWS=1000000
SERVERNAME=Your_Server_Name
PASSWORD=Your_Sybase_Password
USERNAME=Your_Sybase_User_Name
DATABASE=Your_DatabaseName
TABLE_NAME=Your_Table_Name
while [ $i -le $TOTAL_NO_OF_ROWS ]
do
{
j=i+1000-1
bcp $DATABASE..$TABLE_NAME out $TABLE_NAME.$i -F$i -L$j -S$SERVERNAME -U$USERNAME -P$PASSWORD -c -t"|" -r\\n
i=j+1
}
done
0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8135898
Vinit:
What will happened if no of rows less than 1000000?
Is bcp will fail with error?
Is it going to create empty files?.....etc

Also
Are you sure about the following command ?
j=i+1000-1
 I guess you need to use "expr" to do this formula

Extra enhancement, you could query database for row_count of that table and assign the result to unix  variable TOTAL_NO_OF_ROWS


0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8135911
HMK:

 I see you want to bcp out based on query
     select * from table where date = 20010101"

 Here is the idea:

     select *
     into   tempdb..bcp_table
     from   table
     where  date = 20010101"

     bcp tempdb..bcp_table out file.txt -c -t"|" ....etc




0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:Vinit Kain
ID: 8141836
HI  HamdyHassan,

No. of rows can be anything, just put the actual data(select count(*)) in script.

as I declare i and j as integer, I can use mathematical operation, so dont worry about it.

Thanks for your suggestion about count of rows.
To get the no. of rows, add these lines in script

isql -S$SERVERNAME -U$USERNAME -P$PASSWORD -o /tmp/temp.vin <<!
select count(*) from $TABLE_NAME
go
!
TOTAL_NO_OF_ROWS=`awk '{if(NR==3)print $1}' /tmp/temp.vin`

hmk ,if you have write to create table then before executing the script do

select * into temp_table from $TABLE_NAME order by date


so you will get the sorted output by date



0
 

Author Comment

by:hmk
ID: 8151619
I know, you can dump a subset of data in to a temp table  and then bcp out but I am looking for how queries can be used in a bcp command. Can we specify file path (that has query in it) in bcp command if so how ?

Thansk for info.

hmk
0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8151952
Answer is no.

In bcp command, according to syntax , you need to refer to object which is table,....etc

0
 

Author Comment

by:hmk
ID: 8159031
I didn't get what I was looking for. of course there is no such solution exists. So shall i delete question ? The question is worth of 500 points. I don't want to loose them.

THanks,
hmk
0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8159107
If you said "Could anyone help me." and two guys explian the problem and give you some idea of solutions, then ???

sometimes the correct answer to your question is "NO".

Yes, you could ask support for help, refund , reduce no of points,....etc
0
 
LVL 1

Expert Comment

by:Santunes
ID: 8198291

I dont know BCP utility, but if you have space problem you can compress the file on the fly
try this approach:

a) create a pipe
   mknod mypipe p
b) run in background or in another session
   cat mypipe|gzip -c >myfile.gz &
c) run BCP utility
   bcp some_parameters out mypipe others_parameters

I hope this help you.
   
0
 
LVL 1

Expert Comment

by:Santunes
ID: 8198357

Here is how to read the compressed file.

 a) gzip -cd myfile.gz >mypipe &
 
 b) the utility that is going to read the file should use mypipe as its input file.
0
 

Expert Comment

by:modulo
ID: 8240753
Dear expert(s),

A request has been made to close this Q in CS:
http://www.experts-exchange.com/Community_Support/Q_20569053.html

Without a response in 72 hrs, a moderator will finalize this question by:

 - Saving this Q as a PAQ and refunding the points to the questionner

When you agree or disagree, please add a comment here.

Thank you.

modulo

Community Support Moderator
Experts Exchange
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 8260362
Saving this Q as a PAQ and refunding the points to the questionner

modulo

Community Support Moderator
Experts Exchange
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Introduction Regular patching is part of a system administrator's tasks. However, many patches require that the system be in single-user mode before they can be installed. A cluster patch in particular can take quite a while to apply if the machine…
FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.
Suggested Courses
Course of the Month10 days, 8 hours left to enroll

765 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