Import and Export data from SQL Server 2005 to XL Sheet:
In this article, I will explain how to upload the data from an Excel Sheet to a SQL Server 2005 database and also export data from a SQL Server database to an Excel sheet.
For uploading the data from Excel Sheet to SQL Server and viceversa, we need to create a linked server in SQL Server.
Procedure for creating a linked server in SQL Server 2005.
Expample linked server creation:
Before you executing the below command the excel sheet should be created in the specified path and it should contain the name of the columns.
EXEC sp_addlinkedserver 'ExcelSource2',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\Srinivas\Vdirectory\Testing\Marks.xls',
NULL,
'Excel 5.0'
Once you executed above query it will crate linked server in SQL Server 2005.
The following are the Query from sending the data from Excel sheet to SQL Server 2005.
INSERT INTO emp SELECT * from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\text.xls','SELECT * FROM [sheet1$]')
The following query is for sending the data from SQL Server 2005 to Excel Sheet.
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\text.xls;',
'SELECT * FROM [sheet1$]') select * from emp
These two queries are working fine.