CREATE procedure [dbo].[InsertEXCELToSQLSERVER]
as
declare @Field1 nvarchar(50)
declare @Field2 nvarchar(50)
declare @CursorName cursor
set @CursorName = cursor
for
select * from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\path\datafile.xls','select @Field1,@Field2 from [Sheet1$]')
open @CursorName
fetch next from @CursorName into @Field1, @Field2
WHILE @@FETCH_STATUS = 0
BEGIN
select @Field1, @Field2 ;
INSERT INTO Tablename (Field1,Field2) VALUES (@Field1, @Field2 )
fetch next from @UserCursor into @Field1,@Field2
END
CLOSE @CursorName
DEALLOCATE @CursorName
OutPut : following will execute and insert data in to table
exec InsertEXCELToSQLSERVER
