How can i export table data with column name in text file in sql server 2005?
views:
94answers:
3
A:
safest way to do that is to backup data and then restore it on other server! right click on database in ms sql management studio>tasks>backup , choose directory to backup the file. Zip it ( saves a lot of space) move to other location, create database with the same name right click on database>tasks>restore.
In backup and restore always choose overwrite in options menu otherwise it will be appended to existing dataset.
Other way to do that is right click on database, tasks, export data and move it to another location. more advanced though, backup works faster and you supposed to use it unless you want partial move, like some tables and some stored procedures.
eugeneK
2009-10-24 13:58:05
I think Paresh is trying to get data out of the database into some other format (based on other questions, probably Excel), not trying to make a copy of the database.
Aaron Bertrand
2009-10-24 14:00:44
I want to export data in csv file using BCP, but only rows are exported in file. not able to export column name as header.
Paresh
2009-10-24 14:06:57
or any other option are there other than BCP?
Paresh
2009-10-24 14:07:31
I think, i am nit getting proper answer from this discusssion.I have finally added hard coded name of the columns in query and then export data.Please give me idea if any have.
Paresh
2009-12-01 09:32:50
A:
If it's a small amount of data, you can upgrade your Management Studio to SSMS 2008 (you do not need to upgrade your server). The Results tab has the ability to copy data with headers, so you can cut and paste into Excel.
A larger amount of data can be extracted in several ways such as an SSIS package, the Import/Export wizard, or doing a pull through ODBC from Excel.
Stuart Ainsworth
2009-10-24 14:08:04