Import and Export data from SQL Server 2005 to XL Sheet

For uploading the data from Excel Sheet to SQL Server and viceversa, we need to create a linked server in SQL Server.

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