Importing excel to sql server....

Dear All
I am trying to import data from excel sheet to sql server database, by using method 2, it creates a table on fly but it never shows any table in database tables' list but when i execute the code again, system throws an exception that table already exists.
On the other hand method two assumes that there is an existing table in db, but after execution, it never shows data, table remains empty. Here is code, please tell me whats wrong with this code.
Regards
<code>
Dim ExcelConnectionAsNew System.Data.OleDb.OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\annie\anna.xls;Extended Properties=Excel 8.0;")

ExcelConnection.Open()
'For existing Table.............METHOD 1
Dim ExcelCommandAsNew System.Data.OleDb.OleDbCommand("INSERT INTO [User ID=sa;Data Source=CBS101;Initial Catalog=IIETesting;Provider=SQLOLEDB.1;Workstation ID=CBS003].[anna] SELECT * FROM [Sheet1$];", ExcelConnection)

'For new Table.................METHOD 2
Dim ExcelCommandAsNew System.Data.OleDb.OleDbCommand("SELECT * INTO [User ID=sa;Data Source=CBS101;Initial Catalog=IIETesting;Provider=SQLOLEDB.1;Workstation ID=CBS003].[anna] FROM [Sheet1$];", ExcelConnection)

ExcelCommand.ExecuteNonQuery()
ExcelConnection.Close()
</code>

You are missing linked server because Excel only knows Access SQL not T-SQL. Try the code below. Hope this helps.

/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
id name
1 a
2 b
3 c
*/

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Sheet1$

CREATE TABLE test_excel
(id int,
name varchar(255))
GO

INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$

SELECT *
FROM test_excel

/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Table1

SELECT *
FROM ExcelSource...Table2

|||Hi
Thanx for reply but i dont want to create tables own my own because their are more than five hundred excel sheets to import. I need fastest mechanism to imort excel data to sql server!!!
And second problem is that when i export data from sql server to excel then excel is deformating the data e.g. i have a string "000001" in sql server table but excel sheet used to make it 1 (numeric). I am using clipboard to copy data from sql server to excel sheet (It is the only fastest way to export data from sql server to excel).
Please help.|||Execl is not a RDBMS it is a flat file application to do what you want you have two choices DTS on DTSRUN exe or DTS on xp_cmdshell and Sharepoint Portal. Hope this helps.