Import Table Data From MS-Excel

Sample Query:

--Created by :Manikandan.R
--Created Date : nov 11 2010
--Purpose : need to read the files from Excel And need to insert into Source Table

--decalre
declare @TSQLstmt varchar(800)
declare @path nvarchar(50)
declare @filename nvarchar(50)
declare @UserName nvarchar(50)
declare @passwd nvarchar(50)
DECLARE @sheetName nvarchar(50)
DECLARE @ResultExcel table (Col1 int,Col2 nvarchar(245),Col3 nvarchar(245),Col4 int ,Col5 int,Col6 bit)

--------------------------------------------**************need to pass the Excel details by user*************------------------
SET @path='D:\'
SET @filename='sampleFile.xls'
SET @UserName=''
SET @passwd=''
SET @sheetName='Sheet1'
--------------------------------------------**************End*************-------------------------------------------------------

BEGIN try
BEGIN TRANSACTION
SET @TSQLstmt = 'SELECT * FROM OpenDataSource( ' + '''' +
'Microsoft.Jet.OLEDB.4.0' + '''' + ',' +
'''' + 'Data Source="' + rtrim(ltrim(@path)) + '\' +
rtrim(ltrim(@fileName)) + '";User ID='+ltrim(rtrim(@userName)) + ';Password=' +
ltrim(rtrim(@passwd)) + ';Extended properties=Excel 8.0' + '''' +
')...['+ rtrim(ltrim(@sheetName)) + '$]'



INSERT INTO @ResultExcel
EXEC (@TSQLstmt)

--Find Duplicates
delete e2 from @ResultExcel e1 ,@ResultExcel e2 where e1.Col2=e2.Col2 and e1.Col1


--Final Result from Excel
--SELECT Menu_Name,Url,Parent_id,Sort_order,Active FROM @ResultExcel WHERE menu_id is not null
INSERT INTO menu(col1,col2,col3,col4,col5)


SELECT col1,col2,col3,col4,col5 FROM @ResultExcel WHERE col1 is not null



IF @@ERROR != 0 --check @@ERROR variable

BEGIN
ROLLBACK TRANSACTION --RollBack Transaction if Error..
RETURN
END

ELSE
BEGIN

COMMIT TRANSACTION
END

END TRY
BEGIN CATCH
ROLLBACK TRANSACTION

END CATCH