SQL 如何直接从excel更新Sql表？

How to update Sql table from excel directly?

提问by Bodhi

I have an sql database and I am able to connect with excel spreadsheet. But when I update the table from excel directly it's not updating the database and once I click refresh all the entered data is no longer in the excel table

我有一个 sql 数据库，并且可以连接 excel 电子表格。但是当我直接从 excel 更新表时，它不会更新数据库，一旦我点击刷新，所有输入的数据都不再在 excel 表中

Is it possible to update sql database from excel without using any queries?

是否可以在不使用任何查询的情况下从 excel 更新 sql 数据库？

回答by ASH

There are many ways to do this. I'd recommend SSIS. See the link below.

有很多方法可以做到这一点。我会推荐SSIS。请参阅下面的链接。

http://www.sql-server-performance.com/2005/import-data-ssis-excel-sheet-dynamic-sheets/

http://www.sql-server-performance.com/2005/import-data-ssis-excel-sheet-dynamic-sheets/

You can also use VBA to push data from Excel to SQL Server.

您还可以使用 VBA 将数据从 Excel 推送到 SQL Server。

Sub ButtonClick()
'TRUSTED CONNECTION
    On Error GoTo errH

    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strPath As String
    Dim intImportRow As Integer
    Dim strFirstName, strLastName As String

    Dim server, username, password, table, database As String


    With Sheets("Sheet1")

            server = .TextBox1.Text
            table = .TextBox4.Text
            database = .TextBox5.Text


            If con.State <> 1 Then

                con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
                'con.Open

            End If
            'this is the TRUSTED connection string

            Set rs.ActiveConnection = con

            'delete all records first if checkbox checked
            If .CheckBox1 Then
                con.Execute "delete from tbl_demo"
            End If

            'set first row with records to import
            'you could also just loop thru a range if you want.
            intImportRow = 10

            Do Until .Cells(intImportRow, 1) = ""
                strFirstName = .Cells(intImportRow, 1)
                strLastName = .Cells(intImportRow, 2)

                'insert row into database
                con.Execute "insert into tbl_demo (firstname, lastname) values ('" & strFirstName & "', '" & strLastName & "')"

                intImportRow = intImportRow + 1
            Loop

            MsgBox "Done importing", vbInformation

            con.Close
            Set con = Nothing

    End With

Exit Sub

errH:
    MsgBox Err.Description
End Sub

You can also try this, which uses a Where Clause.

你也可以试试这个，它使用 Where 子句。

Sub InsertInto()

'Declare some variables
Dim cnn As adodb.Connection
Dim cmd As adodb.Command
Dim strSQL As String

'Create a new Connection object
Set cnn = New adodb.Connection

'Set the connection string
cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Northwind;Data Source=Excel-PC\SQLEXPRESS"
'cnn.ConnectionString = "DRIVER=SQL Server;SERVER=Excel-PC\SQLEXPRESS;DATABASE=Northwind;Trusted_Connection=Yes"


'Create a new Command object
Set cmd = New adodb.Command

'Open the Connection to the database
cnn.Open

'Associate the command with the connection
cmd.ActiveConnection = cnn

'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
cmd.CommandType = adCmdText

'Create the SQL
strSQL = "UPDATE TBL SET JOIN_DT = '2013-01-22' WHERE EMPID = 2"

'Pass the SQL to the Command object
cmd.CommandText = strSQL


'Execute the bit of SQL to update the database
cmd.Execute

'Close the connection again
cnn.Close

'Remove the objects
Set cmd = Nothing
Set cnn = Nothing

End Sub

回答by Deadsheep39

Yes, you can directly via VBA or with other tools.

是的，您可以直接通过 VBA 或其他工具。

1. via VBA (via qry)
2. via SSIS (https://www.simple-talk.com/sql/ssis/moving-data-from-excel-to-sql-server-10-steps-to-follow/)
3. via managament studio (https://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/)
4. via MS ACCESS (with ODBC connection to server) ...

1. 通过 VBA（通过 qry）
2. 通过 SSIS ( https://www.simple-talk.com/sql/ssis/moving-data-from-excel-to-sql-server-10-steps-to-follow/)
3. 通过管理工作室（https://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/）
4. 通过 MS ACCESS（通过 ODBC 连接到服务器）...