Tuesday, August 25, 2009

Export EXCEL File To SQL Server 2000 /2005 /2008

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

2 comments:

hem said...

WOW! NICE JOB

Bipin Parmar said...

Good to know more about SQL Server through this blog...

Nice one Viral

Regards,
Vanssh