Two ways to import Excel table data into a SQL Server database

Method one, program import

C #, ASP. NET to import data from Excel tables into the corresponding tables in the SQL Server database.
Here we import the data from the Sheet1 table in E:\test.xls into the My_test table in the test database.
Software: Vs2005,sql Server2000,

There are three fields in the Sheet1 table in Test.xls, number, name, comment. The details are shown below.

1. First, we will first create a new my_test table in the test database, which has three fields
tid int type, tname nvarchar type, TT nvarchar type
(Note: The data type in the My_test table must be the same type as the corresponding field in Excel)

2, we use
SELECT * from OPENROWSET (' microsoft.jet.oledb.4.0 ', ' Excel 5.0;database=[excel table. xsl file path]; Hdr=yes;imex=1 ', sheet1$ ')
To read the data in an Excel table, the read data is the same as the data read from the tables in the database, including the field names and data. Of course, we can also use the list of field names to get the department data in Excel tables.
SELECT Field 1, Field 2, Field 3 [...] From OPENROWSET (' microsoft.jet.oledb.4.0 ', ' Excel 5.0;database=[excel table. xsl file path]; Hdr=yes;imex=1 ', sheet1$ ')

3, the first row in Excel is defined as the column name, starting at the 2nd row is the data. The data read from Excel in the SQL statement also starts with the second row, and the two column name becomes the field name. If your first row has a defined column name, the names of the fields that you get from Excel are the column names in Excel. For example: The field names of the data obtained from the Test.xls sheet table are numbered name comments. If the first row of the Excel table you defined does not have a column name defined, then the field name of the data obtained is F1, F2, F3 ... Analogy If you just want to get some of the data from an Excel table, you can use the above content.

4. Create a new Web Form (test.aspx) in VS2005, add a button control to it, and click the button to perform the import. Double-click the button to define the event handler function. The code in Test.aspx.cs is as follows:
Using System;
Using System.Data;
Using System.Configuration;
Using System.Collections;
Using System.Web;
Using System.Web.Security;
Using System.Web.UI;
Using System.Web.UI.WebControls;
Using System.Web.UI.WebControls.WebParts;
Using System.Web.UI.HtmlControls;
Using System.Data.SqlClient;

public partial class Admin_test:System.Web.UI.Page
{
protected void Page_Load (object sender, EventArgs e)
{

}
Public SqlConnection con ()
{
return new SqlConnection ("Server=localhost;uid=test;pwd=test;database=test");
The test in the uid=test here must be system administrtor, otherwise there will be an error
}
protected void Button1_click1 (object sender, EventArgs e)
{
SqlConnection mycon = con ();
String sqlstr = "INSERT into my_test select number, name, Memo from OPENROWSET (' MICROSOFT. JET. oledb.4.0 ', ' Excel 5.0; Hdr=yes;database=e:\\test.xls ', sheet1$) ';
* Here you can use * instead of number, name, remark * *
SqlCommand cmd = new SqlCommand (sqlstr, mycon);
Mycon. Open ();
Cmd. ExecuteNonQuery ();
Mycon. Close ();
}
}

method Two , using SQL Server's own tools to import

1, select the database to insert--right--task--Import data

2, click Next, select the data source, Excel file path, and version information (note: Use the 2010 and above versions of Office, first convert the format to 03 or 07 format for identification)

3, select the target database

4, successful execution

5, to view the import data, you can rename

The above is the step that the database imports Excel file, the database exports the data similarly.