sql server – How to update Sql table from excel directly?

sql server – How to update Sql table from excel directly?

There are many ways to do this. Id recommend something like this, to push data from Excel to SQL Server.

Sub ButtonClick()
    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;
            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
            MsgBox Done importing, vbInformation
            Set con = Nothing
    End With
Exit Sub

    MsgBox Err.Description
End Sub

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

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-PCSQLEXPRESS
cnn.ConnectionString = DRIVER=SQL Server;SERVER=Excel-PCSQLEXPRESS;DATABASE=Northwind;Trusted_Connection=Yes

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

Open the Connection to the database

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

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

Execute the bit of SQL to update the database

Close the connection again

Remove the objects
Set cmd = Nothing
Set cnn = Nothing

End Sub

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

  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)

sql server – How to update Sql table from excel directly?

Leave a Reply

Your email address will not be published. Required fields are marked *