SQL Server- How to write variables and query resul

问题:

How do I write using T-SQL, variable values and query results to a text file, I want to actually keep appending.

回答1:

You can use SQL CLR to write whatever you want out to a file. You can either code this yourself or you can use the SQL# library (I am the author and it is not free -- for this function -- but not expensive either) and use the File_WriteFile function to write out the text. It allows for appending data. The library is free for most functions but not for the File System functions. It can be found at: http://www.SQLsharp.com/

If you want to try coding this yourself, I found this example which is the basic concept I think you are asking for: http://www.mssqltips.com/tip.asp?tip=1662

回答2:

This works but it's a little arcane. Adjust to your query. Essentially you'd be creating command shell echo lines for execution through xp_cmdshell and looping through them.

declare @cmd varchar(500), @minid int, @maxid int;
declare @output table(
    id int identity(1,1) primary key,
    echo varchar(500) not null
    )
set @cmd = 'select top 10 ''ECHO ''+name+'' >> C:\test.txt'' from master.dbo.sysobjects'


insert into @output(echo)
exec(@cmd)

select @minid=Min(ID),@maxid=Max(ID) from @output

while @minid <= @maxid
begin
    select @cmd = echo from @output where id = @minid;
    exec xp_cmdshell @cmd;
    set @minid=@minid+1;
end 

回答3:

Enabling and using xp_cmdshell exposes your instance to additional security risk. Using CLR is safer but also not advised for this purpose. These two options only make a poor design decision worse.

Why are you trying to write to a file from T-SQL? Resist the urge to turn SQL Server into a scripting platform. Use an application language like SSIS, PowerShell or C# to interact with SQL Server and write output to a log file from there as needed.

回答4:

I had problem in this subject, so I want to propose what I did. I Declared a Cursor for Select results and then I write them to file. For example:

 DECLARE @cmd NVARCHAR(400)
 DECLARE @var AS NVARCHAR(50) 
         ,@Count = (SELECT COUNT(*) FROM Students) 
DECLARE Select_Cursor CURSOR FOR 
SELECT name FROM Students OPEN Select_Cursor 
 FETCH NEXT FROM Select_Cursor INTO @var 
WHILE (@Count>0) 
 SET @cmd = 'echo ' + @var +' >> c:\output.txt' 
 EXEC master..xp_cmdshell @cmd 
SET @Count = @Count - 1 
FETCH NEXT FROM Select_Cursor INTO @var 
END
 CLOSE SelectCursor
 DEALLOCATE  SelectCursor

---