How to update Sql table from excel directly?
There are many ways to do this. I'd recommend something like this, to push data from Excel to 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.
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
Update SQL server table from an Excel file
Sorry guys. Just a little bit of fooling around and the nudge from @Ralph helped me figure this out.
Right click the database -> Tasks -> Import
Go through the wizard. When it asks for source and destination files, I selected the Excel sheet as source, Clicked on Edit Mappings
and clicked on Edit SQL
button.
Now I can edit the query to make it do what I want to do.
Thank you.
Related Topics
Why Is There a Scan on My Clustered Index
Most Efficient Method for Persisting Complex Types with Variable Schemas in Sql
How to Set a Jdbc Timeout for a Single Query
Convert Varchar to Datetime in SQL Which Is Having Millisec
Inserting Guid into SQL Server
Oracle SQL to Sort Version Numbers
How to Convert Cyrillic Stored as Latin1 ( SQL ) to True Utf8 Cyrillic with Iconv
What Is This Operand (*= Star-Equals) in SQL Server 2000
Oracle Text Escaping with Curly Braces and Wildcards
Update Multiple Rows Using Case When - Oracle
Sql Server Equivalent of Postgresql Distinct on ()
Attaching an Mdf File Without Ldf File
How to Insert N Rows of Default Values into a Table
Spring Data JPA - Query with The Date Minus 2 Days Not Working
On Duplicate Key Update Feature in H2
Efficiently Querying a Huge Time Series Table for One Row Every 15 Minutes