how can i import file excel in SQL2005

how can i import data from excel file to SQL2005 to create new table or insert data from excel file to table. USING sql statement.
i want to use on C# , on C# i use what connect to control this action.
thanks alot.You could use OPENROWSET table value function for opening any OLEDB datasource:
--Create table XLImport and inserts data:
SELECT * INTO XLImport
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

--Insert data into existed table
INSERT INTO XLImport
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

Other approaches: http://support.microsoft.com/kb/321686|||Thanks for answer me;
But it return error :

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

i'm trying to connect using sql author.. but it return the same error;
help me, thanks alot|||Open Start->Programs->SQLServer...->Configuration....->Surface Area Configuration, then click by Features link and enable 'Ad Hoc Distributed Queries'|||

Konstantin Kosinsky wrote:

Open Start->Programs->SQLServer...->Configuration....->Surface Area Configuration, then click by Features link and enable 'Ad Hoc Distributed Queries'

Still getting same error after configuring

|||

Try this

EXEC sp_configure 'Ad Hoc Distributed Queries', '1';

RECONFIGURE;

|||

Hi try this........

Just copy the below stored procedure and run by passing the required parameter.

Stored Procedure:

create procedure sys.sp_addlinkedserver

@.server sysname, -- server name

@.srvproduct nvarchar(128) = NULL, -- product name (dflt to ss)

@.provider nvarchar(128) = NULL, -- oledb provider name

@.datasrc nvarchar(4000) = NULL, -- oledb datasource property

@.location nvarchar(4000) = NULL, -- oledb location property

@.provstr nvarchar(4000) = NULL, -- oledb provider-string property

@.catalog sysname = NULL -- oledb catalog property

as

-- VARIABLES

declare @.retcode int

-- VALIDATE OLEDB PARAMETERS

if @.provider is null

begin

-- NO PROVIDER MEANS CANNOT SPECIFY ANY PROPERTIES!

if @.datasrc is not null or @.location is not null or

@.provstr is not null or @.catalog is not null

begin

raiserror(15426,-1,-1)

return (1)

end

-- THIS MUST BE A WELL-KNOWN "SQL Server" TYPE (DEFAULT IS SS)

if @.srvproduct IS NOT null AND lower(@.srvproduct) <> N'sql server'

begin

raiserror(15427,-1,-1,@.srvproduct)

return (1)

end

-- USE ALL-NULLS FOR SQL-SERVER PROVIDER

select @.srvproduct = NULL

end

else if @.srvproduct in (N'SQL Server') -- WELL-KNOWN PRODUCT

begin

-- ILLEGAL TO SPECIFY PROVIDER/PROPERTIES FOR SQL Server PRODUCT

raiserror(15428,-1,-1,@.srvproduct)

return (1)

end

else if @.srvproduct is null or lower(@.srvproduct) like N'%sql server%'

begin

raiserror(15429,-1,-1,@.srvproduct)

return (1)

end

-- ADD THE LINKED-SERVER

EXEC @.retcode = sys.sp_MSaddserver_internal @.server,

@.srvproduct, @.provider, @.datasrc, @.location, @.provstr, @.catalog,

1, 0, 0 -- @.linkedstyle, @.localentry, @.dup_ok

return @.retcode

-- SUCCESS

return (0) -- sp_addlinkedserver

For Execution:

sys.sp_addlinkedserver 'XLTEST_SP','Excel','Microsoft.Jet.OLEDB.4.0',

'c:\sample.xls','Excel 8.0'

Just leave Location and Catalog

This will create a Linked Server name called XLTEST_SP.

Then do normal procedure!!!!!!!!!!!!!!!!!!!!!!!