Geek Answers Handbook

Export data from sql server to CSV using ssms

I need to export data from multiple tables in SQL Server 2008 using SSMS. I do not want to use my own data export wizard; Instead, I want to use a query. This means that I cannot use sqlcmd or bcp .

How can I export data from SQL Server 2008 using a query?

I need it to be comma separated and quoted twice as a text specifier.

Thanks so much for any guidance / help.

+4
source share
5 answers

You can run xp_cmdshell to start the bcp operation:

 use [master]; declare @sql nvarchar(4000) select @sql = 'bcp "select * from sys.columns" queryout c:\file.csv -c -t, -T -S'+ @@servername exec xp_cmdshell @sql

Of course, you would have to figure out how to format your qualifiers (perhaps via a format file)

EDIT:

Your original request should be something like strings:

 SELECT IntValue + '"' + CharValue + '"' FROM TABLE

You may also need to enable this feature.

 EXEC sp_configure 'show advanced options', 1; RECONFIGURE; GO EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE; GO
+5
source

You can easily create a CSV output from SSMS, however it does not make quotation marks, so instead of step 6 you can choose a format such as Tab limited:

  • Open a new query window.
  • Create your SQL query.
  • Right-click in the query window.
  • Select query options ...
  • Select the text below the results.
  • Change the output format to comma.
  • Change the maximum number of characters displayed in each column to 8000 or the corresponding value.
  • Click OK.
  • Right-click in the query window.
  • Select the results and results in a file.
  • Complete the request.
  • Select a file name and location.
  • Click "Save."
+7
source

Creating a text file using an SQL query is not possible. SQL is only for retrieving, parsing, updating (etc.) data from a database. To write to a file, you must have executable files / dll sytem.
Is there a specific reason why you want to use SSMS to export the results obtained in csv? Why don't you use SSIS to generate results for csv?

If you use SSIS for this, you create a package to export the information, and then plan the package in the job to run whenever you want.

+3
source

To export an excel file to EXISTING:

 insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;', 'SELECT * FROM [SheetName$]') select * from SQLServerTable

This is dated, but I believe it is still relevant.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Hurrah!

+1
source

You can write the spiced option with an insert.

For example: select 'insert into TargetTable (id, name) (' '' + id + '' ',' '' + name + '' ')' values ​​from SourceTable

-1
source

More articles:


All Articles