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 & "'; "
rakamakafo
  • 1,144
  • 5
  • 21
  • 44
  • [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/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 – marc_s Aug 24 '15 at 15:46
  • Thanks @marc_s, but the issue is definetely not related to your comment – rakamakafo Aug 24 '15 at 15:51

1 Answers1

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
Community
  • 1
  • 1
Jaime Mendes
  • 643
  • 3
  • 9
  • 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 – rakamakafo Aug 24 '15 at 15:13
  • I've tried query by adding smaple values, it works. Maybe there is some kind of syntax error? – rakamakafo Aug 24 '15 at 15:17
  • Hi Sher, my bad. The issue was the Rows.Cell(0) as by default, VBA doesn't start array at position 0 but 1. – Jaime Mendes Aug 24 '15 at 15:23
  • 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 – rakamakafo Aug 24 '15 at 15:31
  • To clarify. My date is at cell A1, and I use it in defaultDate, Range of table values: A2:B4 – rakamakafo Aug 24 '15 at 15:33
  • Can you show me the value of 'bulkSql' when that happens? – Jaime Mendes Aug 24 '15 at 15:37
  • how can I do that? You mean String output? – rakamakafo Aug 24 '15 at 15:41
  • I think one of the problems is decimal separator. Mine is ',', but I guess for sql we need '.'. I've tried this:Replace(Row.Cells(2).Value, ",", "."). Now it is Incorrect syntax – rakamakafo Aug 24 '15 at 15:48
  • That is the correct function. Can you do MsgBox bulkSql before adoCn.Execute? – Jaime Mendes Aug 24 '15 at 15:55
  • Thanks. I'm done, just changed some apostrophes in the query (put it into my post). – rakamakafo Aug 24 '15 at 15:58