1

Suppose I have a table in Excel with two columns (Name, Rate) (say, this table's name tExcel). Table starts at cell (2,1), and Date is static (in cell (1,1))

I want to insert those values into SQL Server 2008 tRate table with following logic

insert tRate(ID, Rate, Date)
   select 
       s.ObjectID, e.Rate, Date   -- date comes from cell(1,1). DateType is smalldatetime
   from 
      tExcel e, tSecurity s
   where 
      e.Name = s.Number

I've created a connection

Sub disp_cust()
Dim adoCN As ADODB.Connection
Dim sConnString As String
Dim sSQL As String
Dim lRow As Long, lCol As Long
Set cn = New ADODB.Connection
sConnString = "Provider=sqloledb;Server=xxx;Database=xxx;User Id=xxx;Password=xxx"
Set adoCN = CreateObject("ADODB.Connection")
adoCN.Open sConnString
adoCN.Close
Set adoCN = Nothing
End Sub

Thanks for help.

EDIT to @jaimetotal answer

sql= "insert tRate(ID, Rate, Date) SELECT s.ObjectId ," & Replace(Row.Cells(2).Value, ",", ".") & ",'" & defaultDate & "' FROM tSecurity s where s.number = '" & row.Cells(1).Value & "'; "
Improve this question
2
  • Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged Commented Aug 24, 2015 at 15:46
  • Thanks @marc_s, but the issue is definetely not related to your comment Commented Aug 24, 2015 at 15:51

1 Answer 1

Reset to default
2

For this sample, I assume that tExcel.Number is the first column and tExcel.Rate is the second. The idea here is to do a for each row in the table (or range) and create an insert/select statement.

Dim rng as Range
Dim defaultDate As string
Dim sql as string, bulkSql as string

Set rng = Range("A1:XX") -- Range of the table. 
defaultDate = Format(Range("A2").Value, "yyyy/mm/dd") 
bulkSql = ""  

'generated sample: insert tRate(ID, Rate, Date) SELECT s.ObjectId, '0.15', '2015/08/24' FROM tSecurity s where s.Number = '007'

For Each row In rng.Rows

    sql= "insert tRate(ID, Rate, Date) SELECT s.ObjectId " & "','" & row.Cells(2).Value & "','" & defaultDate & "' FROM tSecurity s where s.number = '" & row.Cells(1).Value & "'; "

    bulkSql = bulkSql & sql  

Next row

adoCn.Execute bulkSql, , adCmdText

Edit: If you really mean a table, than you can use this sample based from here.

Dim lo As Excel.ListObject
Dim ws As Excel.Worksheet
Dim lr As Excel.ListRow

Set ws = ThisWorkbook.Worksheets(1)
Set lo = ws.ListObjects("tExcel")

'The other code from the previous sample. Use the following ForEach instead

For Each lr In lo.ListRows
   Dim Rate as String
   Dim Number as String

   Rate = Intersect(lr.Range, lo.ListColumns("Rate").Range).Value
   Number = Intersect(lr.Range, lo.ListColumns("Number").Range).Value

   'Generate the query from these values instead   

Next lr
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Thanks, @jaimetotal. First answer seems to be compatible to my needs. I've done all that staff, but I receive error: Runtime error '1004': Application defined or object-defined error. Could you please help. it occurs at line where query is defined
I've tried query by adding smaple values, it works. Maybe there is some kind of syntax error?
Hi Sher, my bad. The issue was the Rows.Cell(0) as by default, VBA doesn't start array at position 0 but 1.
Thanks. One more issue. Now error occurs at line adoCn.Execute bulkSql, , adCmdText. Error: Run-time error '-2147217900(80040e14)': Incorrect syntax near '55'. Sorry for such questions, I wasn't using vba at all
To clarify. My date is at cell A1, and I use it in defaultDate, Range of table values: A2:B4
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.