SQL Server:Export Query as a .Txt File

How to save MSSQL query output to .txt or .json file?

As per the original question, which was tagged as MySQL: Your syntax is wrong. As per MySQL Documentation, FROM clause comes after INTO OUTFILE clause. Following should work in MySQL:

SELECT * INTO OUTFILE '/tmp/orders.txt'
FROM A

In order to get Comma-separated values in the text file, you can do the following instead (in MySQL):

SELECT * INTO OUTFILE '/tmp/orders.txt' 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM A;

For MS SQL Server: There is no INSERT INTO OUTFILE clause available. Instead, a different solution is proposed using SQL Server Management Studio at: https://stackoverflow.com/a/6354143/2469308

How to export query result to text file using command line

Use SQLCMD as below -

    sqlcmd -S . -d DbName -E -s',' -W -Q "SELECT * FROM [Table]" > C:\Test.csv

You can also try BCP as below -

   bcp [BookDb].[dbo].[Books] out C:\Test1.csv -T

Follow below link for more information -
https://www.red-gate.com/simple-talk/sql/database-administration/working-with-the-bcp-command-line-utility/

Export values from SQL Server to txt file

Use a query to collect the variables you want to export. Something like this:

DECLARE @var1 INTEGER
DECLARE @var2 INTEGER

SELECT @var1 = 10
SELECT @var2 = 22

SELECT 'variable 1' AS VarName, @var1 AS VarValue
UNION
SELECT 'variable 2' AS VarName, @var2 AS VarValue

Use this query statement in the following command. Use queryout and replace [querystatement] with the statement above, or use a variable for the query string.

EXEC master..XP_CMDSHELL 'bcp "[querystatement]" queryout "c:\spt_values.dat"'

If the variable needs to be declared outside the statement:

DECLARE @cmd varchar(1000)
DECLARE @sql varchar(8000) 
DECLARE @var1 int
SELECT @var1 = 10
SET @cmd='"select '+CAST(@var1 AS VARCHAR(10))+'"' 
SELECT @sql = 'bcp '+@cmd+' queryout I:\File\mytest.txt -c -t -T -S YAMUNA\SQLEXPRESS';
exec xp_cmdshell @sql;

Exporting SQL query from Python to txt file

Took a bit of tinkering but this was the code I was able to come up with. The thought process was to create import the text file, edit it as a list, then re-export it overwriting the previous list. Thanks for all the suggestions!

RemoveCommas = []
RemoveSpaces = []
AddSymbol = []
Removeextra = []

#Import List and execute replacements
SalesDriversTransactions = []
with open('Sales_Drivers_Transactions.txt', 'r')as reader:
    for line in reader.readlines():
        SalesDriversTransactions.append(line)

for comma in SalesDriversTransactions:
    WhatWeNeed = comma.replace(",","")
    RemoveCommas.append(WhatWeNeed)
    
for comma in RemoveCommas:
    WhatWeNeed = comma.replace(" ","")
    RemoveSpaces.append(WhatWeNeed)
    
for comma in RemoveSpaces:
    WhatWeNeed = comma.replace("þ","þ")
    AddSymbol.append(WhatWeNeed)

for comma in AddSymbol:
    WhatWeNeed = comma.replace("\n","")
    Removeextra.append(WhatWeNeed)

with open('Sales_Drivers_Transactions.txt', 'w')as f:
    for i in Removeextra:
        f.write(i)
        f.write('\n')

How to store output of a SQL Server stored procedure in a .txt file

As the comments and other answers indicate, this is not usually a good idea. But here's how to do it anyway, assuming you're a sysadmin on SQL Server. :)

-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO

CREATE OR ALTER PROCEDURE [dbo].[usp_printresulttofile]
AS
BEGIN
    DECLARE @var NVARCHAR(MAX) = ''
    SET @var = 'print this data in txt file'
    PRINT 'Data is : ' + @var   

    declare @fn varchar(200) = 'c:\temp\out.txt';

    declare @cmd varchar(8000) = concat('echo ', @var, ' > "', @fn, '"');

    print @cmd 
    exec xp_cmdshell @cmd,  no_output

    set @cmd  = concat('type "', @fn, '"');

    print @cmd 
    exec xp_cmdshell @cmd;

END
go
EXEC [dbo].[usp_printresulttofile]

---

Related Topics