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.
Import Data from SQL Server 2005 to Excel Sheet and Viceversa
3 Comments
Join the conversation! Your thoughts help the community grow.
Sign in to leave a comment
It is the same account you read, post and publish with — and you will come straight back to this page.
tharuni chiniPosted Jun 10, 2010, 6:05 AM
Hi i am trying to import data from excel to sqlserver but i am getting error can u plz solve me my code is INSERT INTO empdata SELECT * from OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\text.xls','SELECT * FROM [sheet2$]')
Su TranPosted May 20, 2010, 12:21 AM
very useful thanks very much