Showing posts with label Export Data From Excel to Sql. Show all posts
Showing posts with label Export Data From Excel to Sql. Show all posts

Wednesday, May 20, 2009

SqlQuery To Export Data from Excel to Sql

Hi,
Here is the query to export the data from Excel to sqlserver

CREATE TABLE [dbo].[Addresses_Temp] (
[FirstName] VARCHAR(20),
[LastName] VARCHAR(20),
[Address] VARCHAR(50),
[City] VARCHAR(30),
[State] VARCHAR(2),
[ZIP] VARCHAR(10)
)
GO

INSERT INTO [dbo].[Address_Temp] ( [FirstName], [LastName], [Address], [City], [State], [ZIP] )
SELECT [FirstName], [LastName], [Address], [City], [State], [ZIP]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1',
'SELECT * FROM [Sheet1$]')

To Access the function such as OPENROWSET Go to
MicroSoftSqlServer2005--->ConfigurationTools--->
SqlSurfaceAreaConfiguration

Click on Surface Area Configuration For features
and Enable OPENROWSET and OPENDATASOURCE support