So, you want to run a query in sql server and get the output into a fixed record format flat-file / flatfile?
This solution involves some manual process and the use of excel (though other spreadsheet software may work as well).
There are solutions you can use on windows with command line utilities for SQL server like BCP and sqlcmd.exe, but I think they require padding with spaces and then trimming to a specified length, which you may well be fine with doing.
If you are looking to have a repeatable solution you may have more performance and less headache by using a programming language or scripting language to read the data and create the output in the desired format.
When i say flat-file i mean without character(s) reserved for delimiting like with commas in a .csv file, or tab or vbar(|) etc. as is often the case.
If you’re working with mainframes you may think of RECFM=FB, so LRECL is going to be the same for all records.
- Run your query in SQL server with “Results to grid” option selected
- Copy the output to excel
- Select a column, right click the column header, select “column width” and set to desired length
- Repeat for all cols
- select File -> Save as -> Formatted text (space delimited) (*.prn)
- save your file – this is your fixed record format flatfile!
That’s it, a little manual intervention with excel and you can easily save you data to a flatfile without delimiters.
I have found this very useful for generating once off reports.