insert myTable ( fieldlist ) select ... from openrowset(...) or: insert myTable ( fieldlist ) select ... from opendatasource(...)...sheetname$With SQL 2005 openrowset and opendatasource are disabled by default. You should enable it first (configurator). You can search for samples here in old messages.
using System; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; class xlAppendFromSample { static void Main() { string strSource = "Provider=Microsoft.Jet.OLEDB.4.0;"+ "Data Source=c:\\temp\\sample.xls;Persist Security Info=False;Extended Properties=\"Excel 8.0\""; string strTarget = "Data Source=.\\SQL2005;Initial Catalog=test;Integrated Security=True"; OleDbConnection conSource = new OleDbConnection(strSource); OleDbCommand cmd = new OleDbCommand("select * from [sheet1$]", conSource); conSource.Open(); OleDbDataReader rdr = cmd.ExecuteReader(); SqlConnection conTarget = new SqlConnection(strTarget); SqlBulkCopy cpy = new SqlBulkCopy(conTarget); conTarget.Open(); cpy.DestinationTableName = "dbo.Location"; cpy.ColumnMappings.Add(0, "[Name]"); cpy.ColumnMappings.Add(1, "[CostRate]"); cpy.ColumnMappings.Add(2, "[Availability]"); cpy.ColumnMappings.Add(3, "[ModifiedDate]"); cpy.WriteToServer(rdr); conTarget.Close(); rdr.Close(); conSource.Close(); cpy.Close(); } }PS:Test.dbo.Location is a slightly modified copy of AdventureWorks.Production.Location.