How can I add a file increment when I bcp out? my current bcp overrides every time I run it.

Here is my query.. I need the NAME+ DATE SUBMITTED+ FILE INCREMENT i.e.. 01,02,03 etc...

 SET @FileName    = Convert(varchar(2),DatePart(MONTH,@UseDate))+
 SET @FileLoc     = '\\sangorbandc02\z$\IEHP-Staging'
 SET @Command     = 'bcp "Select * from SGMH_Reports.dbo.FACESHEET" queryout "'+
                     RTRIM(LTRIM(@FileLoc))+'\ProviderID_Facesheet_'+@FileName+'.txt" -S "vparagondb\vparagonsql" -T -c   -t^|'
more ▼

asked Jun 03, 2016 at 09:13 PM in Default

avatar image


Can you confirm that you want to append to an existing file when you run your BCP? Do you mean "overwrite" or "override"?

Jul 17, 2016 at 12:37 AM GPO
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I've lifted this answer verbatim from http://dbaspot.com/sqlserver-programming/359252-append-data-bcp.html in case the link dies some day. Assuming you'll be running BCP from the command line, you can:

 BCP xxx.xxx.xxx OUT Temp.dat -S xxx -T

Then do something like.

 RENAME WholeData.dat Append.dat
 COPY Append.dat+Temp.dat WholeData.dat
 DEL Temp.dat
 DEL Append.dat

Or maybe even:

 TYPE Temp.dat >> WholeData.dat
 DEL Temp.Dat

more ▼

answered Jul 17, 2016 at 07:44 AM

avatar image

4.9k 42 52 58

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 03, 2016 at 09:13 PM

Seen: 77 times

Last Updated: Jul 17, 2016 at 07:44 AM

Copyright 2018 Redgate Software. Privacy Policy