From SQL we can export the data to any database or data stores using DTS packages and Export wizard. Apart from this we can also export data from SQL to any database or datasource using a Query “Insert into OpenRowSet”.
We should all realize that Excel can also store data like access or any other database and Data can be retrieved or written into the excel document.
We are going to use this statement and export the data to excel docoument.
Create procedure dbo.ExportEmployeeData(@filePath as varchar(500))
as
begin
DECLARE @Expr1 VARCHAR(200),@Expr2 VARCHAR(200),@Expr3 VARCHAR(200)
Declare @Sql1 AS VARCHAR(800), @Sql as varchar(800)
SET @Expr1 = '''Microsoft.Jet.OLEDB.4.0'''
SET @Expr2 = '''Excel 8.0;Database=' + @filePath + ';HDR=YES'''
SET @Expr3 = '''SELECT * FROM [Employee]'''
set @Sql1 = @Expr1 + ','+ @Expr2 + ',' + @Expr3
set @SQL='insert into OPENROWSET('+ @Expr1 + ','+ @Expr2 + ',' + @Expr3 + ') SELECT EmployeeID, FirstName, LastName, Title from employees
Exec(@Sql)
end
In this SQL we are taking the excel name with path as the parameter and we are using Microsoft jet OLEDB drives to write the data into the SQL. Where [Employee] in the statement
SET @Expr3 = '''SELECT * FROM [Employee]'''
Is the region we created in the Excel Sheet.
The statement “set @SQL='insert into OPENROWSET('+ @Expr1 + ','+ @Expr2 + ',' + @Expr3 + ') SELECT EmployeeID, FirstName, LastName, Title from employees” actually fetched the records from employees table and inserts it into the Excel sheet.
Now, try executing this stored procedure using the following statement,
ExportEmployeeData 'c:employee.xls'
Notes:
- There should not any data contained in the cells where we will be trying to write the data.
For example, In our case the region is from B4 to E4. and if we are going to insert 10 rows then there should not any data in the cells from B5 to E14.
If any data exists it will throw an error.
- The excel file should be in the SQL Server system or else the folder should be shared with write permission and file share path should be given.
- Also note that, SP is not the only way to write into an excel file, the same logic can be implemented in our java or .net code.
Is it not simple to write the data into an excel sheet. Of course it is.
Now, that we know how the data can be written into an excel sheet, the next step is formatting the data (Presentation). This can be achieved using Excel formulas and Macros.