First execute this procedure
EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO
After executing procedure then write the query
to insert sql table data to excel file.
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=c:\contact.xls;HDR=YES;’,
‘SELECT * FROM [SheetText1$]’)
SELECT * from TableName
_____________________________________________________________________________________
to insert excel data to sql table
Insert into dbo.tableName
select * from openrowset(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;database=C:\contact.xls’,
‘select * from [Sheet1$]’)
NOTE:
Excel columns and Table Columns should be same.