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

bcp out/in

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
hmk
Asked:
hmk
  • 4
  • 2
  • 2
  • +2
1 Solution
 
Vinit KainCommented:
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
 
HamdyHassanCommented:
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
 
HamdyHassanCommented:
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
Industry Leaders: 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!

 
Vinit KainCommented:
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
 
hmkAuthor Commented:
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
 
HamdyHassanCommented:
Answer is no.

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

0
 
hmkAuthor Commented:
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
 
HamdyHassanCommented:
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
 
SantunesCommented:

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
 
SantunesCommented:

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
 
moduloCommented:
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
 
moduloCommented:
Saving this Q as a PAQ and refunding the points to the questionner

modulo

Community Support Moderator
Experts Exchange
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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