Export Excel Data to SQL Server Database

Stored procedure for Exporting data from Excel Sheet to SQL Server Table.

CREATE TABLE [dbo].[MyAddressTable] (
[FirstName] VARCHAR(20),
[LastName] VARCHAR(20),
[ZIP] VARCHAR(10)
)
GO

SELECT * FROM [MyTestDB].[dbo].[MyAddressTable]

INSERT INTO [MyTestDB].[dbo].[MyAddressTable] ( [FirstName], [LastName], [ZIP] )
SELECT [FirstName], [LastName], [ZIP]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\sabin\Try.xls;IMEX=1',
'SELECT * FROM [Sheet1$]')

It does not need to have excel to be installed in the Server machine where SQL Server is installed. All you need to have is a file of .xls in the server.

OPENROWSET featire to be enabled through SQL Server Surface Area Configuration.
Start > SQL Server 2005 > Configuration Tools > Surface Area Configuration

Click Surface Area Configuration for Features
Click on Database Engine and Check the Enable OPENROWSET and OPENDATA..

Done !