June 6, 2008 at 8:19 am
What are some of the tools available for importing from and extracting to text files with SQL 2005, and what are the pros and cons of each?
I'm familiar with the bulk import utility which is great for very large, well-defined text files
xp_cmdshell - just know it exists, don't really know of any of its pros and cons
Of course if you're a programmer you can use VB 2005 or BIDS or something similar, or Access could handle dumping small amounts of data to a text file.
I'm sure there are 3rd-party tools available as well.
Your thoughts?
Thanks
Joe
June 6, 2008 at 8:52 am
I generally use SSIS packages to move data into and out of text files. But my uses for text files generally require that the data be moved into SQL tables, have procs run on the data, then output in a completely different format than the input.
xp_cmdshell and bcp can be used for much the same purpose.
Open Rowset can also be used on some text files.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 6, 2008 at 10:18 am
xp_cmdshell opens a shell, or command prompt, from inside SQL Server. You can't see it, and it can't need user input, but it works like a batch file, allowing you to execute things from the command line.
If you enable it, it's a security hole. ANY command line can be run, ADSI, mail, shutdown the server, del c:\windows, etc.
bcp moves text data, works well for simple stuff. It's blazingly fast.
sqlcmd can be used from a command line to execute scripts, again, formatting is minimal.
If you're a VS-guy, use SSIS. It's programatic, fast, stable, runs well. A touch more overhead, but way more capable in terms of filtering, processing, formatting. Once you built a few packages, you could probably write them as fast as you could format things in bcp.
June 9, 2008 at 8:04 am
I forgot about sqlcmd (formerly osql and isql):
http://msdn.microsoft.com/en-us/library/ms170572.aspx
sqlcmd -S myServer\instanceName -i C:\myScript.sql -o C:\QueryOutputGoesHere.txt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply