SQLCMD or BCP

Question

SQLCMD or BCP

Bhavesh-257515

SSC Enthusiast

Points: 199
More actions
November 20, 2008 at 11:28 pm

#201649

Comments posted to this topic are about the item SQLCMD or BCP

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Dr. Diana Dee SSCarpal Tunnel Points: 4232 More actions · Answer 1

The sqlcmd reference says that a file from a previous sqlcmd will be overwritten. So please tell me the syntax you were referring to that would append to the file.

Marius Els Right there with Babe Points: 762 More actions · Answer 2

I seem to have taken the "append" piece out of the equation :ermm:......

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 3

BCP outputs data to a file, SQLCMD to a file or console, so you can redirect console to a file with > or >>:

rem create output.txt

sqlcmd commands > Output.txt

rem append to output.txt

sqlcmd commands >> Output.txt

RichB SSCrazy Eights Points: 9651 More actions · Answer 4

so why can't you then append the file bcp outputs to the existing file...?

ben.pittoors Old Hand Points: 343 More actions · Answer 5

I got the question correct. However, and it's not the first time that I have noticed this on sqlservercentral. It's not 'DOS'! DOS died a long time ago. Refer to it as 'windows text console' or 'windows command line shell' instead or even cmd.exe 😉

It's got nothing to do with DOS anymore; although it is based on the DOS command line shell (command.com) specifications.

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 6

On the contrary. CMD.exe is DOS. It will execute any well behaved DOS executable, so it provides complete DOC environment. At least 32bit version, 64bit version refuses to run any 16bit application, which DOS applications normally are.

Additionally, it provides DOS compatible text mode console (terminal) for windows applications with all standard devices as stdin, stdout, stderr, nul, prn, lpt1, redirection, etc.

Windows 3.x and older, 95, 98, ME were all graphics shell on top of DOS, while NT series does not depend on DOS.

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 7

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.

Best Regards,

Chris Büttner

ben.pittoors Old Hand Points: 343 More actions · Answer 8

Robert (11/21/2008)
On the contrary. CMD.exe is DOS. It will execute any well behaved DOS executable, so it provides complete DOC environment. At least 32bit version, 64bit version refuses to run any 16bit application, which DOS applications normally are.
Additionally, it provides DOS compatible text mode console (terminal) for windows applications with all standard devices as stdin, stdout, stderr, nul, prn, lpt1, redirection, etc.
Windows 3.x and older, 95, 98, ME were all graphics shell on top of DOS, while NT series does not depend on DOS.

I beg to differ. If anything COMMAND.COM was the command line shell of DOS and CMD.EXE is that of Windows. It may be compatible with some legacy DOS apps (but not that many... hence the existence of dos emulators like DOSBOX etc.) but that doesn't mean it is the same thing.

More info: wikipedia to the rescue

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 9

You're free do disagree. DOS was an OS at the time and command.com was its command shell. It abstracted access to some devices like printer, serial ports and disks. If provided file system. It had nothing to do with graphics cards, sound cards etc. CMD.EXE does the same thing, provides the DOS environment and a small part of hardware emulation (0xA000-0xFFFF mapping). So, DOS applications using this work perfectly, not knowing they're in windows application. Some applications (mostly games) needed hardware access to graphics and/or sound card, or other. For these you need an application like dosbox to emulate direct hardware access, which works great, runs almost any game without problems even on 64bit system.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 10

Was not >> an original UNIX command?

Regards,Yelena Varsha

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 11

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Anipaul SSC-Insane Points: 24681 More actions · Answer 12

Interesting one... To learn more read this

http://msdn.microsoft.com/en-us/library/ms175995.aspx

🙂

Slick84 SSCertifiable Points: 5602 More actions · Answer 13

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 SSCrazy Eights Points: 9651 More actions · Answer 14

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.