[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 33795
  • Last Modified:

MS SQL Server Management Studio export table to csv file - how to

Hello,

We are new to MS SQL Server Management Studio and need to export a table to a csv formatted file. We figure there must be a wizard or some function to do this but have not found it.  Could you give us simple instructions on how to do this.  Thanks.

PS: Searching the knowledge base only gives us solutions outside of MS SQL Server Management Studio. We need solution using MS SQL Server Management Studio if possible.

0
RegisCorp
Asked:
RegisCorp
2 Solutions
 
SQL_SERVER_DBACommented:
You can run the bcp command in the command shell with the correct parameters.

if you type bcp /? you get the parameters
\bcp /?
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]

Or you could run the BCP command in a stored procedure on SQL like this
EXEC  master..xp_cmdshell @bcpCommand
When you run the command in SQL you should make sure that your user has enough rights to run the bcp command

This is an example:
select @message = 'BCP "SELECT * FROM TABLE" ' +
     'queryout \\SERVERNAME\FOLDER1\SUBFOLDER1\FILENAME.CSV' /c /t ; /r \n /SSQLSERVERNAME /T'

print @message
EXEC  master..xp_cmdshell @message
0
 
Jai STech ArchCommented:
open SSMS --> right click on the database --> select export
select the data source (you db name)...select the destination...select the tables you need to export and complete the wizard
0
 
ZberteocCommented:
BULK INSERT Northwind.dbo.[Order Details]
   FROM 'f:\orders\lineitem.tbl'
   WITH
      (
         FIELDTERMINATOR = '|',
         ROWTERMINATOR = '|\n'
      )

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
ZberteocCommented:
Sorry, that was import. Here:

with User/Password:
bcp "SELECT au_fname, au_lname FROM dbname.owner.tablename" queryout C:\Temp\tablename.txt -c -Sservername -Usa -Ppassword


with trusted connection:
bcp "SELECT au_fname, au_lname FROM dbname.owner.tablename" queryout C:\Temp\tablename.txt -c  -Sservername -T

rund this command from command prompt, the files will be created on the local machine, or from Query Analyzer with:
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM dbname.owner.tablename" queryout C:\Temp\tablename.txt -c -Sservername -Usa -Ppassword"'

EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM dbname.owner.tablename" queryout C:\Temp\tablename.txt -c  -Sservername -T'


in which case the files will be created on the machine where SQL server to which the connection was.
0
 
ArcadianZCommented:
So in all Microsofts' over-developed automations, they can't make a simple import/export function that creates a file to transport from one machine to another machine, allowing what the author of this thread intended. This just blows my mind.

The question still remains, which is why this should not be closed yet.

How does one "export" a table with structure and data intact to a file(WITH ALL DATA-TYPES ACCEPTED), then walk to another machine with M$ studio, and upload it with creating custom SQL or shell commands?

For the love of all that's good! Oy!
0
 
AndrewLundgrenCommented:

There's an easier way.

In MSSMS, run a select of the whole table.
eg: SELECT * FROM MyTableName

Then, right click on the results and click "Save Results As..."

Cheers
--
Andrew
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now