October 24, 2009 at 2:59 pm
In my job, I use a lot of delimited text files.
I have written a SQL query, and would like to put the results in a delmited file to use for other applications.
However, each method of exporting out of SQL Server causes me issues:
1) Clipboard: Copying from the clipboard provides the cleanest data, but will only work when the record number is small.
2) Right click on the data and save as: Produces a comma delimited file. Commas are a bad delimiter for the data I work with.
3) Export as a File: Correct Delimiters, but it spits the data across multiple lines instead of keeping each row in a single line. Ultimately that's not usuable for the work I do. I've also had problems with truncations using this method.
Are there any better methods of exporting data?
October 24, 2009 at 3:37 pm
This sounds like a job for SSIS. For the ad-hoc extracts you seem to describe, the "import-export" wizard would be the easiest way to go as it builds the SSIS code for you, and you don't even need to keep it to use it once on the fly and then let it get cleaned up. That utility isn't always easy to find (at least it wasn't part of the default shortcuts in my installation), but you should find it in the program files, likely at "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe".
To use it, start it up and pretty much follow your nose. Pick source type, server name, and database name, then a destination type -- you want a flat file and specify "delimited". Tell the wizard where you want the output and then whether you want a whole table or the results of a query. I find it easier to build my query in SSMS with its color-coding editor, syntax checking and ease of testing and then copy it to the plain text editor in the wizard.
Note that "Import" and "Export" are really the same thing. You can use a CSV or Excel file to populate a db table or Excel worksheet. Oh, and one more thing: since SSIS is NOT part of SQL Server Express, I would think that the import/export wizard would be missing from that edition also.
October 24, 2009 at 4:28 pm
sounds like a good case for the 'queryout' option in the command line utility BCP. If it is a lot of data it will be faster than SSIS.
---------------------------------------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply