To import data from excel to DB

Here,I introduce some methods to import data from excel to DB,

1  By OPENDATASOURCE

SELECT * FROM OPENDATASOURCE(
    'Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;DataBase=D:\TEST.xls')...[sheet1$]

Note: Sometimes, error message will occor when executing above script like this:
Msg 15281, Level 16, State 1, Line 1
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.

The solution issetting Ad Hoc Distributed Queries to 1,you can refer to my post: http://www.cnblogs.com/Burgess/archive/2008/09/24/1298195.html

2   By Linked server

EXEC sp_addlinkedserver  --add linked server
    @server = N'MyExcel',
    @srvproduct = N'Jet 4.0',
    @provider = N'Microsoft.Jet.OLEDB.4.0',
    @datasrc = N'd:\TEST.xls',
    @provstr = N'Excel 8.0'
GO

Note:You can also add linked server by below method:

exec sp_addlinkedsrvlogin 'MyExcel','false' --login without account (Optional)
go

select * from MyExcel...sheet1$  --query data
go

 

 3  By VBA

 

 1Private Sub cmdInsert_Click()Sub cmdInsert_Click()
 2
 3    Dim LinCnt As Integer
 4    LinCnt = 6
 5    If InputBox("Please input password1", "口令输入框", , 8700, 4700) = "password" Then
 6        Rows("6:6").Select
 7        Rows("6:65536").Select
 8        Range("A6").Select
 9        
        Connection.Open " Provider=SQLOLEDB.1;Persist Security Info=True;User ID=User_id;Password=PWD; Initial Catalog=DB_name;Data Source=Server_ip;Connect Timeout=60 "
        Connection.CursorLocation = adUseClient
        CMD.ActiveConnection = Connection

        Do While Cells(LinCnt, "C") <> ""
            If txtSoldto.Text = "" And txtQcimat.Text = "" And Cells(LinCnt, "G") = "" Then
                MsgBox "Please maintain "
                Exit Sub
            Else
                SQLstmt = " insert into table_name values('" & VBA.Trim(txt1.Text) & "','" & VBA.Trim(txt2.Text) & "','" & Cells(LinCnt, "C") & "','" & Cells(LinCnt, "D") & "','" & Cells(LinCnt, "E") & "','" & Cells(LinCnt, "F") & "','" & Cells(LinCnt, "G") & "','" & Cells(LinCnt, "H") & "','" & Cells(LinCnt, "I") & "','" & Cells(LinCnt, "J") & "') "
                CMD.CommandText = SQLstmt
                CMD.Execute
                LinCnt = LinCnt + 1
            End If
        Loop
        Connection.Close
        Exit Sub
    Else
        MsgBox "You have no right to insert!"
        Exit Sub
    End If
End Sub

 4   By SQL Server Import and Export Wizard
Detailed oprating steps is abbreviated here.

5  Other methods:
Please refer to http://support.microsoft.com/default.aspx/kb/321686