December 6, 2015 at 10:45 pm
I know, this is probably a strange question, but i've been tasked with getting a marketing user two year's worth of data so that he can send it off to a third-party vendor for marketing analysis. Problem is that i'm having a hell of a time trying to get it to him. I've decided that BCP is the best option, i'm querying all fields in a table out to a flat file. Because of the volume of data, i'm doing this by month, but i'm getting HUGE files and I don't know why. 1.6 million rows should not result in a file which is 400 gigabytes, LOL. Clearly i'm doing something wrong, but as I rarely work with BCP i'm not sure exactly what that is.
Can someone take a look at my BCP command?
BCP "select * from MyTable WHERE date_start_lead between '2014-07-01' AND '2014-07-31'" queryout G:\Leads3.txt -SUSW2DW1 -T -k -dEDW -c -t
December 7, 2015 at 12:49 am
Yours:
BCP "select * from MyTable WHERE date_start_lead between '2014-07-01' AND '2014-07-31'" queryout G:\Leads3.txt -SUSW2DW1 -T -k -dEDW -c -t
Add "|" or "\t" after -t and I bet your file size shrinks considerably. Right now BCP is delivering a fixed-width file instead of a delimited one so most of the data is likely whitespace.
Try one of these:
BCP "select * from MyTable WHERE date_start_lead between '2014-07-01' AND '2014-07-31'" queryout G:\Leads3.txt -SUSW2DW1 -T -k -dEDW -c -t "|"
BCP "select * from MyTable WHERE date_start_lead between '2014-07-01' AND '2014-07-31'" queryout G:\Leads3.txt -SUSW2DW1 -T -k -dEDW -c -t "\t"
If you expect pipes or tabs in your data you can choose something less traditional like ~ or even employ multiple characters like "|~|".
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 7, 2015 at 11:12 am
Orlando that worked, my export is running very fast and easy now. Thanks for the help, BCP is sort of new to me but I see the value.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply