is there any better way to export SQL server data into csv file?

  • 892717952

    SSC-Addicted

    Points: 453

    I have  questions of exporting SQL server data, the details as below, thanks!

    1. how to use BCP export data from SQL server (table or view or stored procedures) into a csv file (supposing the file path is d:\temp) ?
    2. is there any better way to fast export SQL server data into csv file ?
  • Grant Fritchey

    SSC Guru

    Points: 396288

    If you're really looking to automate and control this, SSIS is the magic place for data movement within SQL Server.

    However, if you want to stick to a command line driven mechanism, I'd look to two choices.

    First, SQLCMD.EXE. This can absolutely control export to a CSV file. BCP is ancient technology, only there for backwards compatibility. SQLCMD is a better choice all round. It's easier to script and control, supports parameters and more.

    Second, PowerShell is your buddy. You can easily export to a CSV file using a PowerShell command. Even more control than SQLCMD and you can combine your PowerShell scripts with the DBATools module to get really crazy with your automation.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 995652

    Heh... no, Grant... you and I are ancient technology. 😀  BCP is still a nasty fast work horse.  While it wasn't designed to handle true CSV exports nor even a straight comma separated version, it still works fine and fast.  While I understand the lure of SSIS to do such a thing, I'm close enough to say that I'll give it up and retire first before I put an SSIS package in play. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Grant Fritchey

    SSC Guru

    Points: 396288

    Yeah, I am ancient. I'd still pick SQLCMD or PowerShell over bcp though.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • ScottPletcher

    SSC Guru

    Points: 98334

    Are you exporting the data to load into a different SQL Server instance?  If so, then you should "native" format on the output.  That will save a lot of time on the subsequent load.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ZZartin

    SSC-Dedicated

    Points: 30413

    What is the context for what you're trying to do, how are you running the export and how big is the data set?  There are a number of options, bcp, SSIS, sqlcmd, powershell that all have their own advantages and disadvantages in terms of setting up and running.

  • Jeff Moden

    SSC Guru

    Points: 995652

    Grant Fritchey wrote:

    Yeah, I am ancient. I'd still pick SQLCMD or PowerShell over bcp though.

    Heh... guess I'm more ancient.  I've not found much that will actually beat BCP for performance for the types of things I've had to do.  To each their own, though.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ScottPletcher

    SSC Guru

    Points: 98334

    Jeff Moden wrote:

    Grant Fritchey wrote:

    Yeah, I am ancient. I'd still pick SQLCMD or PowerShell over bcp though.

    Heh... guess I'm more ancient.  I've not found much that will actually beat BCP for performance for the types of things I've had to do.  To each their own, though.

    I agree that BCP is normally very performant.  Sometimes you need a format file, which is often a real pain to get working correctly, but once you do BCP works great.

    The best part of SSIS is the very easy async exec available.  I would use SSIS if I needed to run multiple data moves at the same time.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 892717952

    SSC-Addicted

    Points: 453

    ScottPletcher wrote:

    Are you exporting the data to load into a different SQL Server instance?  If so, then you should "native" format on the output.  That will save a lot of time on the subsequent load.

    No, I only export data from SQL Server into a file (csv or other format file )

  • Jeff Moden

    SSC Guru

    Points: 995652

    ScottPletcher wrote:

    Jeff Moden wrote:

    Grant Fritchey wrote:

    Yeah, I am ancient. I'd still pick SQLCMD or PowerShell over bcp though.

    Heh... guess I'm more ancient.  I've not found much that will actually beat BCP for performance for the types of things I've had to do.  To each their own, though.

    I agree that BCP is normally very performant.  Sometimes you need a format file, which is often a real pain to get working correctly, but once you do BCP works great.

    The best part of SSIS is the very easy async exec available.  I would use SSIS if I needed to run multiple data moves at the same time.

    Totally agreed on parallel loads whether asynchronous or synchronous.  Makes it easy.  Flow control is one of the things that I think SSIS is good at.

    Like you probably have, I've also done such things without SSIS.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 892717952

    SSC-Addicted

    Points: 453

    ScottPletcher wrote:

    Jeff Moden wrote:

    Grant Fritchey wrote:

    Yeah, I am ancient. I'd still pick SQLCMD or PowerShell over bcp though.

    Heh... guess I'm more ancient.  I've not found much that will actually beat BCP for performance for the types of things I've had to do.  To each their own, though.

    I agree that BCP is normally very performant.  Sometimes you need a format file, which is often a real pain to get working correctly, but once you do BCP works great.

    The best part of SSIS is the very easy async exec available.  I would use SSIS if I needed to run multiple data moves at the same time.

    As I want to make a report to let user export data from SQL Server database,  if we use SSIS  to export data, and if IT guys set the SSIS package in the backend, how can we let user use it in the frontend ? thanks!

  • Jeff Moden

    SSC Guru

    Points: 995652

    892717952 wrote:

    ScottPletcher wrote:

    Jeff Moden wrote:

    Grant Fritchey wrote:

    Yeah, I am ancient. I'd still pick SQLCMD or PowerShell over bcp though.

    Heh... guess I'm more ancient.  I've not found much that will actually beat BCP for performance for the types of things I've had to do.  To each their own, though.

    I agree that BCP is normally very performant.  Sometimes you need a format file, which is often a real pain to get working correctly, but once you do BCP works great.

    The best part of SSIS is the very easy async exec available.  I would use SSIS if I needed to run multiple data moves at the same time.

    As I want to make a report to let user export data from SQL Server database,  if we use SSIS  to export data, and if IT guys set the SSIS package in the backend, how can we let user use it in the frontend ? thanks!

    That's a part of the reason I don't do such things and, instead, will write stored procedures that the user can execute without having privs to anything else in the system.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Grant Fritchey

    SSC Guru

    Points: 396288

    892717952 wrote:

    ScottPletcher wrote:

    Jeff Moden wrote:

    Grant Fritchey wrote:

    Yeah, I am ancient. I'd still pick SQLCMD or PowerShell over bcp though.

    Heh... guess I'm more ancient.  I've not found much that will actually beat BCP for performance for the types of things I've had to do.  To each their own, though.

    I agree that BCP is normally very performant.  Sometimes you need a format file, which is often a real pain to get working correctly, but once you do BCP works great.

    The best part of SSIS is the very easy async exec available.  I would use SSIS if I needed to run multiple data moves at the same time.

    As I want to make a report to let user export data from SQL Server database,  if we use SSIS  to export data, and if IT guys set the SSIS package in the backend, how can we let user use it in the frontend ? thanks!

    I'm going to ditto Jeff, but say that in this case, I'd go with PowerShell as the magic behind the report.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • ZZartin

    SSC-Dedicated

    Points: 30413

    892717952 wrote:

    ScottPletcher wrote:

    Jeff Moden wrote:

    Grant Fritchey wrote:

    Yeah, I am ancient. I'd still pick SQLCMD or PowerShell over bcp though.

    Heh... guess I'm more ancient.  I've not found much that will actually beat BCP for performance for the types of things I've had to do.  To each their own, though.

    I agree that BCP is normally very performant.  Sometimes you need a format file, which is often a real pain to get working correctly, but once you do BCP works great.

    The best part of SSIS is the very easy async exec available.  I would use SSIS if I needed to run multiple data moves at the same time.

    As I want to make a report to let user export data from SQL Server database,  if we use SSIS  to export data, and if IT guys set the SSIS package in the backend, how can we let user use it in the frontend ? thanks!

    Well what is the front end?

  • Jeff Moden

    SSC Guru

    Points: 995652

    Grant Fritchey wrote:

    892717952 wrote:

    ScottPletcher wrote:

    Jeff Moden wrote:

    Grant Fritchey wrote:

    Yeah, I am ancient. I'd still pick SQLCMD or PowerShell over bcp though.

    Heh... guess I'm more ancient.  I've not found much that will actually beat BCP for performance for the types of things I've had to do.  To each their own, though.

    I agree that BCP is normally very performant.  Sometimes you need a format file, which is often a real pain to get working correctly, but once you do BCP works great.

    The best part of SSIS is the very easy async exec available.  I would use SSIS if I needed to run multiple data moves at the same time.

    As I want to make a report to let user export data from SQL Server database,  if we use SSIS  to export data, and if IT guys set the SSIS package in the backend, how can we let user use it in the frontend ? thanks!

    I'm going to ditto Jeff, but say that in this case, I'd go with PowerShell as the magic behind the report.

    Or a simple batch file that makes the call with a "trusted" connection based on whomever the user is.

    Of course, I have to ditto ZZartin's observation about not knowing what the "front end" actually is.  We need to know that so we can determine what kind of call to make.  It might simply be a call to a properly setup stored procedure.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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