Writing data back to SQL from Excel sheet
If you want to have the Excel file do all of the work (retrieve from DB; manipulate; update DB) then you could look at ActiveX Data Objects (ADO). You can get an overview at:
http://msdn.microsoft.com/en-us/library/ms680928(VS.85).aspx
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
INSERT data from Excel into SQL DB
You can do a lot with ADO:
Dim cn As New ADODB.Connection
''You should probably change Activeworkbook.Fullname to the
''name of your workbook
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& ActiveWorkbook.FullName _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
cn.Open strCon
s = "INSERT INTO [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=Server;Trusted_Connection=Yes;" _
& "DATABASE=test].SomeTable ( Col1, Col2, Col3, Col4 ) " _
& "SELECT a.Col1, a.Col2, a.Col3, a.Col4 " _
& "FROM [Sheet2$] a " _
& "LEFT JOIN [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=Server;Trusted_Connection=Yes;" _
& "DATABASE=test].SomeTable b ON a.Col1 = b.Col1 " _
& "WHERE b.Col1 Is Null"
cn.Execute s
You can also use the ACE connection: http://www.connectionstrings.com/ or OPENROWSET and an SQL Server connection. In all cases, you may have problems with mixed data types in columns, depending on your registry settings (http://forum.lessthandot.com/viewtopic.php?f=17&t=12043&p=59669&hilit=excel#p59669)
transfer data from Excel to SQL Server
You can do this easily using SSIS, you can refer to these two links for full details.
- Link 1
- Link 2
[EDIT]
If you have Express then you can try the below commands to setup a linked server and get the data
EXEC sp_addlinkedserver ExcelData,'Jet 4.0','Microsoft.Jet.OLEDB.4.0','C:\MyData.xls', NULL, 'Excel 5.0;'
GO
Then you can select the data into your tables
INSERT INTO Listings ...
SELECT column1 AS intListingID, <put all columns here> FROM ExcelData...Data
GO
For other options check this link
Generate sql insert script from excel worksheet
I think importing using one of the methods mentioned is ideal if it truly is a large file, but you can use Excel to create insert statements:
="INSERT INTO table_name VALUES('"&A1&"','"&B1&"','"&C1&"')"
In MS SQL you can use:
SET NOCOUNT ON
To forego showing all the '1 row affected' comments. And if you are doing a lot of rows and it errors out, put a GO between statements every once in a while
How to create an SQL table and and populate it with Excel spreadsheet data?
If the data is not that big and if it is a simple table, easiest way is to create and open the table in SQL Server Management Studio and copy paste the excel data into it.
Other solutions are using DTS or using SSIS..
Related Topics
How to Get Current Database and User Name with 'select' in Postgresql
Duplicating Records to Fill Gap Between Dates
SQL Convert Milliseconds to Days, Hours, Minutes
Represent a Subquery in Relational Algebra
Formatting Clear and Readable SQL Queries
Pagination with The Stored Procedure
How to Find N Consecutive Records in a Table Using Sql
Duplicate Postgresql Schema Including Sequences
How to Get Column Names from a Query in SQL Server
Sql: Count() Based on Column Value
How to Use Max() on a Subquery Result
Mod' Is Not a Recognized Built-In Function Name
Parsing Nested Xml into SQL Table
Better Way to Write Large Sqls Inside Rails Models
How to Increment Value in Postgres Update Statement on JSON Key