As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread:
We're funding a new Acquisitions Incorporated series on Kickstarter right now! Check it out at

SQL query help

SiliconStewSiliconStew Registered User regular
Due to a bug in our accounting software, I need a way to create a flat text file from a sql view. But the file must have header and footer rows around each set of detail rows. I normally just use a BCP query to export the view rows as is to a text file, but due to the bug I need to export them to this other format. I've tried sql reporting services and can easily add the headers and footers there, but there is no export renderer for plain text as it's displayed in the report. At best, you'd have to do a bunch of text manipulation after the fact through a custom app/script to make that work.

Here's the normal view that's used for export:

TranNumber, TranTotal, Item, Qty, Price, Location
1111, 5.00, ItemA, 1.00, 5.00, A
2222, 7.00, ItemB, 2.00, 2.00, A
2222, 7.00, ItemC, 1.00, 1.00, A
2222, 7.00, ItemD, 4.00, 0.50, A
3333, 11.00, ItemC, 0.25, 4.00, A
3333, 11.00, ItemE, 1.00, 10.00, A

The file needs to list the transactions as simple fixed width strings with header and footer rows like so:

"00" & TranNumber & TranTotal
"01" & Item & Qty & Price <-this detail line repeats for as many items as are in the transaction
"02" & Location

So the final output file for the view above would be (all numbers padded 2 leading digits, 2 trailing digits for example):


What query, stored procedure, SQL Integration Services, or otherwise could I use to do this? If it can be done with only a single (however complex) query, which I doubt, that would be best. Temporary tables can be used if needed, stored procedures created, the SaleHeader and SaleDetail tables that the view was built from can be used. Ideas, solutions? Assume I know little about SQL here.

Just remember that half the people you meet are below average intelligence.
SiliconStew on


  • BigityBigity Lubbock, TXRegistered User regular
    edited January 2014
    Couldn't you use the command shell and bcp to do this in a store procedure?

    Something like this? Course, this is for XP, not sure what/if anything changes for 7 and up.

    EXEC master.dbo.sp_configure 'show advanced options', 1
    EXEC master.dbo.sp_configure 'xp_cmdshell', 1

    EXEC xp_cmdshell 'bcp "SELECT * FROM SYS.DATABASES" queryout "C:\bcp.txt" -T -c -t,'

    Bigity on
  • SiliconStewSiliconStew Registered User regular
    edited January 2014
    My problem isn't with how to run a stored procedure or export results, but rather what does that stored procedure need to do to format the data the way I need it.

    SiliconStew on
    Just remember that half the people you meet are below average intelligence.
  • DaxterMaxDaxterMax Registered User regular
    edited January 2014
    I'm going to make some general assumptions based on the data given in the example.
    The view is denormalized and TranNumber, TranTotal and Location are coming from some header table or a combination of a SUM and header? As the same data is repeated for each line item.

    I could be way off the mark here but maybe something like this?

    This is based solely on the example of the view table data shown, and I am calling that view "SalesView" in the example code.
    If this is along the right track, it could be made more efficient by probably using the underlying tables; SalesHeader and SalesDetail
    A CTE could probably be used to get the Header and Footer information in one query, rather than the two I am using.

    This is all written from memory so it could be completely bust and have typos, as I don't have SQL Server installed.

    Edit: That won't actually format your numbers correctly either as I'm just doing a straight conversion to string

    DaxterMax on
    Steam | DaxterMax | Youtube
  • SiliconStewSiliconStew Registered User regular
    @DaxterMax That certainly looks like it might work, and impressive doing it from memory. The only thing I had to change was this:


    otherwise it would only put a header and footer row around the first transaction in the query. Not going to claim victory yet, lots more work and testing to do and I've been cockblocked on every seemingly minor issue on this project, but this gives some hope that I won't be doing everything manually. Thanks.

    Just remember that half the people you meet are below average intelligence.
  • DaxterMaxDaxterMax Registered User regular
    Oops yeah, overlooked that :# otherwise the row number would just keep incrementing.
    You might just need to sort out of the numeric formatting after that.

    a STUFF() or RIGHT() with some CASTS might get you there. SQL isn't great with built-in functions for some of those kind of things.

    Steam | DaxterMax | Youtube
Sign In or Register to comment.