How do I copy all tables in database to Excel

▲
0
▼

I have a SQL Server 2008 database and I wanted to quickly copy all the data in the database into either separate Excel spreadsheets or into a single spreadsheet in separate tabs. Putting the data into separate CSV files is also ok. How do I do this without having to do a select * from for each of the tables and then copying the data into Excel? There are a lot of tables.

EDIT

Running the wizard to export data I get this error on Pre-execute

  • Pre-execute (Error)

    Messages Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. (SQL Server Import and Export Wizard)

    Error 0xc0202025: Data Flow Task 1: Cannot create an OLE DB accessor. Verify that the column metadata is valid. (SQL Server Import and Export Wizard)

    Error 0xc004701a: Data Flow Task 1: component "Destination - ApprovalStatuses" (25) failed the pre-execute phase and returned error code 0xC0202025. (SQL Server Import and Export Wizard)

Thanks,

Sachin

sql-server
asked on Stack Overflow Dec 21, 2012 by Sachin Kainth • edited Dec 21, 2012 by Sachin Kainth

4 Answers

▲
1
▼

Using Server Management Studio:

Server Management Studio (right click on db) > 
  Tasks > 
      Export > 
         Login > 
            Destination box select Microsoft Excel > ...

If you are on a 64-bit server then;

Windows > Start Button > 
     All Programs >
         MS Sql Server [your version] > 
             USE Import and Export Data (32-bit)
answered on Stack Overflow Dec 21, 2012 by Kaf • edited Dec 21, 2012 by Kaf
▲
1
▼

See this link http://www.66pacific.com/sql_server_export_to_excel.aspx

Using the SQL Server Import and Export Wizard, you can easily copy tables from the database to the spreadsheet.

answered on Stack Overflow Dec 21, 2012 by mashtheweb
▲
1
▼

You can directrly export table from database to excel file using sql-server Import and Export Wizard.

Have look at this article about Exporting SQL Server Data to Excel.

answered on Stack Overflow Dec 21, 2012 by huMpty duMpty
▲
-1
▼

Ok, see this link http://www.hotscripts.com/forums/windows-net-programming/59962-export-data-database-excel-via-sql-command-customize-style-vb-net-without-auto.html for a quick way of doing it.

Ideally, if you get time to program it, I would use RDLC to build custom reports which you can export out as Excel using DataTables.

See my blog post at https://mashupweb.wordpress.com/2012/02/08/creating-an-online-list-of-users-in-a-database-using-report-wizard/ on how export data onto Excel using RDLC.

answered on Stack Overflow Dec 21, 2012 by mashtheweb

User contributions licensed under CC BY-SA 3.0