x

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))+
                    Convert(varchar(2),DatePart(DAY,@UseDate))+
                    Convert(varchar(4),DatePart(YYYY,@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

yflores
1

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

GPO
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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x57

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