Hi
I was wondering if SQL Server 2008 had an OUTPUT TO command?
I am wanting to execute a query and output it to a .txt file. Ideally I need each column value to have single quote marks around them 'value1'
I have seen many comments about BCP, but I haven't come across this before.
Any help would be greatly accepted.
The SQL Engine returns a recordset. It is the application that decides what to do with that recordset. SSMS and BCP are both applications and they are capable of directing the data to a file. But SQL itself can't.
OK, are these programmes capable of creating a text file based on a 'Trigger'
The SQL Engine just returns data to an application. It has no notion of text files. So, no, a trigger won't solve this for you either.
You may be able to find some backdoor mechanism to do what you want. I'd recommend NOT doing this. Let SQL do what it does and let the application do what it does. BCP may be the right approach for you; I don't know what your needs are. If you are running the code manually, SSMS has the capability to write to a file and have quotes around the strings.
Actually, yes, although I don't recommend it actually be done by code within a trigger. The process of writing to a file just takes relatively too long. Instead of the trigger doing the actual export to a file, the trigger should make a note in a "control" table and a job that runs on a regular basis should scan the control table to see if it has any work to do.
The job itself can run a command-line level command (CmdExec Step) that uses either BCP or SQLCMD to execute a query against the server/database to create the required output to a file. Although BCP has no actual support for a class of files known as CSV, you can create a BCP Format file that, when combined with a little T-SQL prestidigitation, will allow exquisite control over what the column-based output (sometimes called a "flat file") will look like. You could also do similar with a "Powershell" step (although my tendency is to avoid curly-brace languages).
You can also use xp_CmdShell to do the same things with even more flexibility and control over the process. Unfortunately, most DBAs and Developers have been brainwashed into believing that it's a security risk and nothing could be further from the truth if it's used properly. Best of all is that it can all be done by stored procedure, which makes code distribution to other machines/databases very simple and non-error prone due to "configuration changes". What that also means is that I don't need to expand the security surface area of the system by using SSIS or other tools and you don't need to learn or support a whole new application or language to do it properly.
As a bit of a sidebar, using single quotes to encapsulate individual elements in the output is totally non-standard. Even an import to EXCEL will require a little bit of special handling (need to identify the quoted identifier) in such cases. A part of the CSV standard is to use double-quotes consistently if such a thing is needed. If you follow the real CSV standard, only text-based columns need to be encapsulated and, unlike what most programs seem to spit out, requires the field to be encapsulated on every line in the file and not just the lines/elements that contain embedded delimiters. As a best practice, you should also make the delimiters and quoted text identifiers apply to the header (if present) as well.