Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36

    Unanswered: Bulk INSERT INTO with SELECT

    Hi,

    I have a SQL statement that inserts records from one table to another.

    The statement works fine and goes like this

    Private Function PopulateReceiverQueue1() As Boolean
    ' Create Inventory list
    On Error GoTo PopulateReceiverQueue1_Click_Err

    ' Assume success
    PopulateReceiverQueue1 = True

    ' Populate candidate received queue
    Dim conConnection As ADODB.Connection
    Dim rstSource As ADODB.Recordset
    Dim rstTarget As ADODB.Recordset

    Dim StrSQLSource As String
    Dim StrSQLTarget As String
    Dim flgTrans As Boolean

    Set conConnection = CurrentProject.Connection

    StrSQLSource = " SELECT * FROM [Materials]" & _
    " WHERE [Warehouse]='" & Me!CustomerID & "'" & _
    " AND [UnitsInStock] >0" & ";"

    Set rstSource = New ADODB.Recordset
    rstSource.CursorLocation = adUseClient
    rstSource.CacheSize = 20
    rstSource.Open StrSQLSource, conConnection, adOpenStatic, dLockOptimistic, adCmdText

    flgTrans = True
    conConnection.BeginTrans

    If Not rstSource.EOF Then

    StrSQLTarget = " SELECT * FROM [Orders supplies];"

    Set rstTarget = New ADODB.Recordset
    rstTarget.CursorLocation = adUseClient
    rstTarget.CacheSize = 20
    rstTarget.Open StrSQLTarget, conConnection, adOpenStatic, adLockOptimistic, adCmdText

    ' Use Recordset for single record
    Do While Not rstSource.EOF

    rstTarget.AddNew
    rstTarget![OrderID] = Me![OrderID]
    rstTarget![ProductID] = rstSource![MaterialID]
    rstTarget![SupplyToWarehouse] = rstSource![Warehouse]
    rstTarget.Update
    rstSource.MoveNext
    Loop

    rstTarget.Close
    Set rstTarget = Nothing

    End If

    flgTrans = False: conConnection.CommitTrans

    WrapUp:
    rstSource.Close
    conConnection.Close

    Exit_PopulateReceiverQueue1_Click:
    Set rstSource = Nothing
    Set conConnection = Nothing
    Exit Function

    PopulateReceiverQueue1_Click_Err:
    If flgTrans Then conConnection.RollbackTrans: flgTrans = False
    If Not rstSource Is Nothing Then
    If rstSource.State = adStateOpen Then rstSource.Close
    End If
    If Not conConnection Is Nothing Then
    If conConnection.State = adStateOpen Then conConnection.Close
    End If
    PopulateReceiverQueue1 = False
    LogMsgError Err.Description, Err.Number, ModuleName$, "PopulateReceiverQueue1"
    Resume Exit_PopulateReceiverQueue1_Click
    End Function

    ----------

    However I am trying to execute using command but so far without success

    My new code goes like this

    StrSQL = "INSERT INTO [Orders supplies] (OrderID, ProductID, SupplyToWarehouse) Values " & _
    "(" & _
    " SELECT OrderID AS OrderID, MaterialID AS ProductID, Warehouse AS SupplyToWarehouse" & _
    " FROM [Materials] WHERE [Warehouse]='" & Me!CustomerID & "'" & _
    " AND [UnitsInStock] >0" & _
    ");"

    If Not uRunSQL(StrSQL, "") Then GoTo WrapUp


    The problem is in SQL string. Thanks in advance for any ideas.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You either use a VALUES clause or a SELECT clause, not both. Drop the VALUES and the parentheses around the SELECT.
    Paul

  3. #3
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36
    There is a little complication OrderID is taken from the current form outside the SQL statement

    I tried this without success

    StrSQL = " INSERT INTO [Orders supplies] (OrderID, ProductID) " & _
    Me!OrderID & _
    " SELECT MaterialID AS ProductID FROM [Materials] " & _
    " WHERE [Warehouse]='" & Me!CustomerID & "'"

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That's invalid SQL; that needs to be the first field in the SELECT clause, to match up with the field in the INSERT INTO clause. It's sort of out in the middle of nowhere like that. Concatenate it in the same way you did the customer (without the single quotes if it's a numeric value).
    Paul

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    Debug.print StrSQL
    Run that when you have finished building your string. It will make your debugging 20 times easier. If you aren't really comfy with SQL and VBA then it makes it a lot easier to spot the error(s).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36

    It works...

    It works and here is the new function:

    ---------------------------------------------------------
    Private Function PopulateReceiverQueue1() As Boolean
    On Error GoTo PopulateReceiverQueue1_Click_Err

    ' Populate queue
    StrSQL = " INSERT INTO [Orders supplies] (OrderID, ProductID, SupplyToWarehouse) " & _
    " SELECT " & Chr$(34) & Me!OrderID & Chr$(34) & " AS OrderID, " & _
    " MaterialID AS ProductID, " & _
    " Warehouse AS SupplyToWarehouse " & _
    " FROM [Materials] " & _
    " WHERE [Warehouse]='" & Me!CustomerID & "'"

    ' Execute Bulk Insert Command
    If Not uRunSQL(StrSQL, "") Then GoTo PopulateReceiverQueue1_Click_Err

    WrapUp:
    PopulateReceiverQueue1 = True

    Exit_PopulateReceiverQueue1_Click:
    Exit Function
    PopulateReceiverQueue1_Click_Err:
    PopulateReceiverQueue1 = False
    Resume Exit_PopulateReceiverQueue1_Click
    End Function

    ---------------------------------------------------------

    Thank you very much for your suggestions.
    Last edited by danisapfirov; 08-13-09 at 12:31.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •