Is there a way to create a fixed width flat file from a SQL query using the SQL language? If so, how?
Yes and no. No, not with just SQL, because an SQL query just produces a result set, which is actually a table, which it then "hands back" to whatever interface or program or API you submitted the query through -- Query Analyzer, PHPMyAdmin, Toad, Access, etc.
The first thing to check is whether your database has a utility for this purpose. For example, SQL Server has DTS (Data Transformation Services), which can use an existing SQL query to produce a text file.
If your database has nothing like this, or if you do not have access to it, you can still "fake it" if you can highlight and copy the query result set's rows and columns.
For example, in Access, after you run a query, you will be in datasheet view; just right-click the top left corner, and select Copy. Now you can paste the result set as rows and columns into Excel, and then get Excel to save the worksheet as a Formatted Text (Space delimited) *.prn file.
Even if all you have is a text editor, you can still do it. Create a "pre-formatted" result set which consists essentially of just one long column. For example, consider this query:
select smallintcol , varchar30col , datetimecol , nullablechar1col from yourtable
Rewrite it like this (SQL Server syntax):
select right('00000'+cast(smallintcol as varchar(5)),5) + left(varchar30col+space(30),30) + convert(char(10),datetimecol,120) + cast(coalesce(nullablechar1col,' ') as char(1)) from yourtable
The result set of this query will have only one columm, which you can then paste into a plain text editor and save as a flat file, without worrying about variable-length fields or tabs between them.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.