SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQLCMD or BCP


SQLCMD or BCP

Author
Message
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8894 Visits: 600
Was not >> an original UNIX command?


Regards,
Yelena Varshal

Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18735 Visits: 12426
It's technically correct that bcp.exe can only replace the contents of a file and output from SQLCMD can be used to append as well, and if the purpose of the question is to remind people of that, then it's a good question.

However, I would not endorse this as a best practice. If I had to append the results of a query to an existing file, I'd either use bcp to output to a temporary file and then concatenate the two together, or I'd start investigating the possibilities of SSIS.

Remember, bcp.exe is written specifically to quickly import or export large amounts of rows, and is optimized for this purpose, while sqlcmd.exe is a general purpose querying tool that is optimized for ad-hoc queries. Also, bcp.exe gives the user lots more control over the formatting of the results.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9755 Visits: 1407
Interesting one... To learn more read this
http://msdn.microsoft.com/en-us/library/ms175995.aspx

Smile



Slick84
Slick84
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1634 Visits: 1163
Btw.. not sure if anyone clicked on the links but the BCP link actually led to an MSDN article about Creating Indexes with Included columns... or is that where it's supposed to go? I thought it would tell me more about BCP rather than index creation. Anyway, thought i'd just add this here...

--
Hehe
RichB
RichB
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2998 Visits: 1065
Christian Buettner (11/21/2008)
RichardB (11/21/2008)
so why can't you then append the file bcp outputs to the existing file...?

RichardB, you cannot pipe your data out with BCP like with SQLCMD.


No - but whats difficult about outputting it to a new file and appending it to the one you want?
its a whole extra line of code, but hardly a drama.



Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8894 Visits: 600
Richard,

Drama starts after appending!
Now with automatic appending they will have to write another 100 lines of code to prompt for the timely cleanup, checking for the file size, drive space, implementing file roll over etc. Not even talking about the process that will consume the output data, how this process will determine what are the new data. Don't tell me about the log files, they are not normally created by BCP process.


Regards,
Yelena Varshal

Vinay Thakur-585143
Vinay Thakur-585143
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2247 Visits: 790
I think i could not get the question correct, as BCP we can append data into table. but it overrites flat file.SmileSmile

Thanx.
Vinay


http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
SQLCHILD
SQLCHILD
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 63
Dear Sir , your wrote: " I'd either use bcp to output to a temporary file and then concatenate the two together".

Please tell me that how would this be implemented.
I want that when i insert a record in a table in sql then a trigger is fired on the insert , which executes a stored procedure , the procedure appends the new record into a text file in my C drive.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search