Thank you for the reply. I tried but was not successful to make linked server to excel work. Though I used openrowset query with INSERT INTO and it solved my issue 🙂
[Code]
Insert into MyDb1.dbo.MyTable1
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\List.xls', 'Select * from [Sheet1$]')
[/code]
and then I am using the imported data in the table to do the required calculations. Thanks for the help