How To Use a Stored Procedure To Create an Excel Spreadsheet

Save

Stored procedures are coded database objects that allow programmers to retrieve data from tables. Some companies require you to retrieve data and export it to an Excel spreadsheet. You can do this in a SQL stored procedure. The procedure retrieves data using a SQL query and sends the results directly to your Excel spreadsheet. Creating a stored procedure allows you to execute the exportation of records at any time without recreating the code.

  • Create your stored procedure name. Every stored procedure requires a name. The following code starts a stored procedure:

    create proc newProc ()

  • Insert the stored procedure code that queries the database and exports data to Excel. The code to create the export process only requires one SQL statement. To export data from a table to Excel:

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=C:\theExcelFile.xls;', 'select Name from [Sheet1$]') select FirstName, LastName from Customer

    The first line of code opens the Excel database and sets the driver, which is Jet.OLEDB for Excel spreadsheets. The next section specifies the path for the spreadsheet and the sheet. The first sheet in an spreadsheet is \"Sheet1$.\" The final select statement is the records retrieved from the database to export.

  • Press the F5 key to execute the statement. This saves the stored procedure in your database. To execute the stored procedure and export data, enter the following code into the SQL editor:

    exec newProc

References

  • Photo Credit number background image by kuhar from Fotolia.com
Promoted By Zergnet

Comments

You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!