Hi,

I have 20-30 SQL database files. How can I to export the data into Excel
files automatically or by batch (using macros)? Any built-in command in sql
server can help?
Thanks
Simon

Simon Kwong (SimonKwong (AT) discussions (DOT) microsoft.com) writes:
If you just want the data in a format that Excel can read, you could
use BCP which is a command-line program:

BCP db..tbl out tbl.csv -c -t; -T

db..tbl - is your database and table.
out - you are exporting data.
tbl.csv - the output file.
-c - character format. Use -w if you have Unicode data.
-t; - Semicolon as separator. What is the expected delimiter in Excel
for a CSV file depends on regional settings. In my case it's
semicolon, but it may be comma for you.
-T - Login with integrated security; use -U and -P if you use an
SQL login.

Note that this will not include any column headers in the file.

Another option is to look at the Export and Import wizard, which you
find under Tasks when you right-click a database in Mgmt Studio.

And finally, you may want to look at SQL Server Integration Services,
which is the prime tool for export and import of data. (But which I've
never used myself.) Here you can design packages, schedule them etc.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx