SQLCMD or BCP

  • Bhavesh-257515

    SSC Enthusiast

    Points: 199

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

  • Dr. Diana Dee

    SSCarpal Tunnel

    Points: 4232

    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: 760

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

  • Robert-378556

    SSCertifiable

    Points: 5542

    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

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

  • ben.pittoors

    Old Hand

    Points: 343

    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: 5542

    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

    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

    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: 5542

    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: 34217

    Was not >> an original UNIX command?

    Regards,Yelena Varsha

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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

    Interesting one... To learn more read this

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

    🙂

  • Slick84

    SSCertifiable

    Points: 5602

    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

    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.

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

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