Quick BCP question

Quick BCP question

Post by Paul » Sun, 22 Dec 2002 05:25:48



Hi,

Is it possible to have BCP queryout append an existing file, rather than
  overwrite it? If not, can someone suggest a way to achieve this
behaviour?

Thanks,

Paul

 
 
 

Quick BCP question

Post by Joel Aske » Sun, 22 Dec 2002 06:17:14


Paul:

You could do it in 3 NT command lines:

This example assumes you have an original file named "orig.dat", and your
bcp output file is "temp.dat"

1. [ BCP  out to temp.dat ]
2. Use this command: copy orig.dat + temp.dat orig.dat
3. del temp.dat


Quote:> Hi,

> Is it possible to have BCP queryout append an existing file, rather than
>   overwrite it? If not, can someone suggest a way to achieve this
> behaviour?

> Thanks,

> Paul


 
 
 

Quick BCP question

Post by John Bel » Sun, 22 Dec 2002 07:03:18


What is the second file?

Assuming it is a BCP file with the same columns then why not union the query?

If not.... then why not just issue a copy a+b c statement?

John

On Fri, 20 Dec 2002 15:25:48 -0500, Paul


>Hi,

>Is it possible to have BCP queryout append an existing file, rather than
>  overwrite it? If not, can someone suggest a way to achieve this
>behaviour?

>Thanks,

>Paul

 
 
 

Quick BCP question

Post by Steve Kas » Sun, 22 Dec 2002 07:55:45


Does it work to specify the output destination with >> myOutputFile.txt
instead of with -o ?

Steve Kass
Drew University


> Hi,

> Is it possible to have BCP queryout append an existing file, rather
> than  overwrite it? If not, can someone suggest a way to achieve this
> behaviour?

> Thanks,

> Paul

 
 
 

Quick BCP question

Post by oj » Sun, 22 Dec 2002 07:31:23


Here's an example...

exec master..xp_cmdshell 'bcp "select top 1 * from pubs..authors" queryout
"c:\t1.txt" -T -c -Smysrv'
exec master..xp_cmdshell 'bcp "select top 2 * from pubs..authors" queryout
"c:\t2.txt" -T -c -Smysrv && pushd c:\ && type t2.txt>>t1.txt'

--
-oj
http://www.rac4sql.net


> What is the second file?

> Assuming it is a BCP file with the same columns then why not union the
query?

> If not.... then why not just issue a copy a+b c statement?

> John

> On Fri, 20 Dec 2002 15:25:48 -0500, Paul

> >Hi,

> >Is it possible to have BCP queryout append an existing file, rather than
> >  overwrite it? If not, can someone suggest a way to achieve this
> >behaviour?

> >Thanks,

> >Paul

 
 
 

Quick BCP question

Post by oj » Sun, 22 Dec 2002 08:25:02


"-o" is not an option flag for bcp. you must be thinking of 'osql'.

--
-oj


> Does it work to specify the output destination with >> myOutputFile.txt
> instead of with -o ?

> Steve Kass
> Drew University


> > Hi,

> > Is it possible to have BCP queryout append an existing file, rather
> > than  overwrite it? If not, can someone suggest a way to achieve this
> > behaviour?

> > Thanks,

> > Paul

 
 
 

Quick BCP question

Post by Steve Kas » Sun, 22 Dec 2002 08:44:25


Wow.  Your answer was longer than mine.

SK


>Here's an example...

>exec master..xp_cmdshell 'bcp "select top 1 * from pubs..authors" queryout
>"c:\t1.txt" -T -c -Smysrv'
>exec master..xp_cmdshell 'bcp "select top 2 * from pubs..authors" queryout
>"c:\t2.txt" -T -c -Smysrv && pushd c:\ && type t2.txt>>t1.txt'

>--
>-oj
>http://www.rac4sql.net



>>What is the second file?

>>Assuming it is a BCP file with the same columns then why not union the

>query?

>>If not.... then why not just issue a copy a+b c statement?

>>John

>>On Fri, 20 Dec 2002 15:25:48 -0500, Paul

>>>Hi,

>>>Is it possible to have BCP queryout append an existing file, rather than
>>> overwrite it? If not, can someone suggest a way to achieve this
>>>behaviour?

>>>Thanks,

>>>Paul

 
 
 

Quick BCP question

Post by Steve Kas » Sun, 22 Dec 2002 09:04:18


Huh?  My copy of BOL has -o  :

bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-m max_errors] [-f format_file] [-e err_file]
    [-F first_row] [-L last_row] [-b batch_size]
    [-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6]
    [-q] [-C code_page] [-t field_term] [-r row_term]
    [-i input_file] [-o output_file] [-a packet_size]
    [-S server_name[\instance_name]] [-U login_id] [-P password]
    [-T] [-v] [-R] [-k] [-E] [-h "hint [,...n]"]

SK


>"-o" is not an option flag for bcp. you must be thinking of 'osql'.

>--
>-oj



>>Does it work to specify the output destination with >> myOutputFile.txt
>>instead of with -o ?

>>Steve Kass
>>Drew University


>>>Hi,

>>>Is it possible to have BCP queryout append an existing file, rather
>>>than  overwrite it? If not, can someone suggest a way to achieve this
>>>behaviour?

>>>Thanks,

>>>Paul

 
 
 

Quick BCP question

Post by oj » Sun, 22 Dec 2002 09:45:00


Just checked bol, you're right. There is an '-o' flag. But it's not useful
at all in this case. It's nothing more than just *statistics* for bcp
operation.

exec master..xp_cmdshell 'bcp "pubs..authors" out
"c:\t1.txt" -T -c -o"c:\t2.txt" && type c:\t2.txt'

--
-oj


Quote:> Huh?  My copy of BOL has -o  :

> bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
>     {in | out | queryout | format} data_file
>     [-m max_errors] [-f format_file] [-e err_file]
>     [-F first_row] [-L last_row] [-b batch_size]
>     [-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6]
>     [-q] [-C code_page] [-t field_term] [-r row_term]
>     [-i input_file] [-o output_file] [-a packet_size]
>     [-S server_name[\instance_name]] [-U login_id] [-P password]
>     [-T] [-v] [-R] [-k] [-E] [-h "hint [,...n]"]

> SK

 
 
 

Quick BCP question

Post by oj » Sun, 22 Dec 2002 09:46:44


hehehhe...gotta top you every now and then. ;)

--
-oj


Quote:> Wow.  Your answer was longer than mine.

> SK

 
 
 

Quick BCP question

Post by Steve Kas » Sun, 22 Dec 2002 11:03:30


You sure did - I was thinking of osql, and silly not to test it...
Gotta go delete the files "-t" and "-c" from my winnt/system32
directory now...

SK


>hehehhe...gotta top you every now and then. ;)

>--
>-oj



>>Wow.  Your answer was longer than mine.

>>SK

 
 
 

Quick BCP question

Post by Paul » Tue, 24 Dec 2002 22:31:52


Thank you gentlemen!! That's considerably easier than CASTing as CHAR
the data in the columns as part of a UNION (which was not sorting the
way I wanted anyhow).

Paul


> Here's an example...

> exec master..xp_cmdshell 'bcp "select top 1 * from pubs..authors" queryout
> "c:\t1.txt" -T -c -Smysrv'
> exec master..xp_cmdshell 'bcp "select top 2 * from pubs..authors" queryout
> "c:\t2.txt" -T -c -Smysrv && pushd c:\ && type t2.txt>>t1.txt'

> --
> -oj
> http://www.rac4sql.net



>>What is the second file?

>>Assuming it is a BCP file with the same columns then why not union the

> query?

>>If not.... then why not just issue a copy a+b c statement?

>>John

>>On Fri, 20 Dec 2002 15:25:48 -0500, Paul

>>>Hi,

>>>Is it possible to have BCP queryout append an existing file, rather than
>>> overwrite it? If not, can someone suggest a way to achieve this
>>>behaviour?

>>>Thanks,

>>>Paul