ahthomas (1/2/2013)
I have a script that writes data to a temp table. I need to automatically export this to a csv located at a UNC destination. How do I write this into the SQL script? A retention period in this would also be nice...
here's an example using bcp that i just tested:
--I like using a super special 4 char row delimiter to be sure it doesn't exist in the data
--flags explanation:
-- -c = character data
-- -t"[||]" = field terminator instead of comma?
-- -r"[~~]" = row terminator instead of CrLf \n
-- -T' = Trusted connection
--out
declare @cmd varchar(4000)
SELECT @cmd = 'bcp.exe' --the executable...if not in the PATH variable, you might need something like 'C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\bcp.exe '
+ ' ' --space required for parameterizing
+ '"SELECT EMAILTEMPLATESID, EMAILBODY FROM BFONRA.dbo.EMAILTEMPLATES ORDER BY EMAILTEMPLATESID"'
--<-- the query itself: no CrLf allowed.
+ ' ' -- space required for parameterizing
+ 'queryout' -- bcp parameter for query direction
+ ' ' -- space required for parameterizing
+ 'c:\Data\bcpExample2.txt' -- destination file name:
+ ' ' -- space required for parameterizing
+ '-c' -- -c = character data
+ ' ' -- space required for parameterizing
+ '-t","' -- comma delimited -t"[||]" = field terminator
+ ' ' -- space required for parameterizing
+ '-t"\n"' -- -r"[~~]" = row terminator
+ ' ' -- space required for parameterizing
+ '-T"' -- -T' = Trusted connection
EXECUTE master.dbo.xp_cmdshell @cmd
Lowell