Create a Data Extract using T-SQL

Posted on 2011-10-27
Last Modified: 2012-06-21
I need to create an extract that runs for a customer that they can upload. It would be nice to be able to create this and then let the customer just run the report and have the report automatically create a file somewhere that she can grab and upload to her site.

There is a header1 row
Header2 row
Header 3 row
then data.. some of the rows are null.

I have done this type of thing before but it has been years.. and could use some help.

Here is a example of what I need..
Oh and it can be tab or comma delimited

Question by:Jennifer Barman
    LVL 39

    Expert Comment

    You could use SQL bcp like in example below or SQL SSRS to create a nice report that can be sent via email fo a mailinglist when snapshot updated:

    exec master..xp_cmdshell N'sqlcmd -E -Q"select * from server_name.dba_name.schema_name.table_name" -h-1 -s"," -W -o"c:\output.txt"', no_output

    --for TEXT fields use BCP!!!
    LVL 100

    Expert Comment

    Are you trying to privide data for  user to run reports or do you want t runthe report against the data and upload say a PDF copy of the result?


    Author Comment

    by:Jennifer Barman
    what I "ultimatly" need is a .csv file. Now I thought because the user needs the file on no certain schedule, that it might be easy for her to run the report and have it export into a file for her.. BUT.. really all I need is to write a SQL script.

    Author Comment

    by:Jennifer Barman
    I am not sure I have access to SSRS.. :(

    I do on "MY" test server.. maybe if I create it.. I can then have my other department upload it. Our system is that they have granted me access to the Database that I use.. there are 40 databases.. I know I cant look at the analyzer for log issues.. cause the root of the sql tree (if I'm saying that right) is locked.
    LVL 35

    Accepted Solution


    From SSMS just run a script and save the results and you have your text file.

    The only extra thing is to now add commas and delimters to the output.

    select someInt, someString
    from dbo.SomeTable

    select convert( varchar( 20 ), someInt ) + ',"' + someString + '"'
    from dbo.SomeTable


    Author Comment

    by:Jennifer Barman
    Let me give this a try...dtodd...
    LVL 83

    Expert Comment

    You said you want the customer to have the ability to run something(like a report) which generates the csv file. Could this not be a webpage?
    LVL 18

    Expert Comment

    There is a free tool: Report Genie ( It is pretty old and I am not sure that it will server your needs but it may worth to check it. What it can do s to retrieve data from SQL Server and export it to a file .

    Another one which is free for one task is :
    It supports SQL queries and the result can be exported to excel. pdf, csv ... saved to local network, document management or SharePoint and send by e-mail. This tool supports also Crystal Reports and SSRS.

    The first tool is completely free ( double check this )  while the second one is commercial but you can use it to run one task free.

    If you have SSIS you can create a package that will do the same without using external tools.
    LVL 39

    Expert Comment

    --export to EXCEL from T-sql code

    USE [AdventureWorks];
    INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
    'SELECT * FROM [Sheet1$]')
    SELECT TOP 5 FirstName, LastName
    FROM Person.Contact

    --using BCP with column names:
    bcp "select 'ProductID', 'Name', 'ProductNumber' union all select convert(varchar(10), ProductID), Name, ProductNumber from AdventureWorks2008R2.Production.Product" queryout "c:\temp\product.xls" -c -T


    Author Closing Comment

    by:Jennifer Barman
    it only partically answered my q

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now