Last post Jul 28, 2015 12:28 PM by eralper
Jul 26, 2015 09:44 PM|malina|LINK
the below one worked for me to export to text file using sql
DECLARE @sql VARCHAR(8000);
SELECT @sql = 'bcp " SELECT col1 ,col2,col3 from table" queryout "C:\abc\edg\test.txt" -c -t, -T -S' + @@SERVERNAME;
exec master..xp_cmdshell @sql;
but ,Now i get all the data in a single line.
how can design the text file to show every column in different line
Can any body help me on this...
Jul 28, 2015 01:36 AM|Krunal Parekh|LINK
Not sure that you want exact format or not but there are few methods in which you can export the query into a comma separated file.
1. Use Save Results as CSV.
2. Use OpenRowSet to export using sql query.
exec sp_configure 'show advanced options', 1;RECONFIGURE;exec sp_configure 'Ad Hoc Distributed Queries', 1;RECONFIGURE;GOINSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\;HDR=YES;FMT=Delimited','SELECT * FROM [Deposit.csv]')
SELECT * FROM dbo.Deposit
Here you will need to enable ad hoc distributed queries. also you may need to install access database engine. also the csv file with columns must be present before hand.
3. Use SQLCMD
SQLCMD -S YourSQLServer -d YourDatabase -U YourUserName -P YourPassword -Q “Your Query” -s “,” -o “C:\Yourfilename.csv”
Hope this helps.
Jul 28, 2015 12:28 PM|eralper|LINK
On my development database, I made an implementation as follows
You can convert it to your table structure
First of all I had to create a SQL view
create view ClientView as
with cte as (
rn = row_number() over (order by ClientId),
ClientId, ClientName, ClientAddress, ClientCity
select top 100 Percent DataColumn
rn, 1 as colOrder, 'ClientId:' + convert(nvarchar(100),ClientId) as DataColumn
rn, 2 as colOrder, 'ClientName:' + ClientName as DataColumn
rn, 3 as colOrder, 'ClientAddress:' + ClientAddress as DataColumn
rn, 4 as colOrder, 'ClientCity:' + ClientCity as DataColumn
) t order by rn, colOrder
Above view displays data of a table row sequentially with column names concatenated before its value.
I had provide a unique value like returned from
SQL Row_Number() function to each column
Please check error that might rise due to conversion like int to string types
One last trick was to use
TOP 100 Percent clause in SQL View to use ORDER By . This helped me to keep the BCP command simple
Then I called BCP command for
data export from SQL Server
DECLARE @cmd varchar(4000)
SET @cmd = 'bcp "SELECT * FROM [kodyaz.development].dbo.ClientView" queryout "c:\temp\textfile3.txt" -c -UTF8 -T -Slocalhost'
EXEC master..xp_cmdshell @cmd
I hope that helps