Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Insert Into from Union? (2000)

    I'm trying to avoid having saved queries in this application. So I'm running an append query (the SQL text save in a variable) from code. This works fine. But now I need to add another table and UNION it to the rest of the SELECT part of my original query. Here's a shortened example of what I am doing.

    strSQLAppend = "INSERT INTO table1 (field1) SELECT field1 FROM table2 WHERE field = criteria UNION SELECT field1 FROM table3 WHERE field = criteria;"

    I'm getting an error when this runs that goes like this:

    Syntax error (missing operator) in query expression "WHERE field = criteria UNION SELECT field1 FROM table3 WHERE field = criteria"

    Is this not possible? Can't I insert data into a table from a union SQL statement? (I can post the original code if someone needs to look at it, but my example is the gist.)

    Sarah

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Insert Into from Union? (2000)

    Hi sarah,

    I'm afraid that you can't combine a UNION query and an append or make-table into one. As far as I know, the only way to do it is to store the UNION query and then build other queries upon it. Sorry!

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Insert Into from Union? (2000)

    That's what I was afraid of.

    But can I do this...?

    Somehow store the Union query in a variable (recordset, querydef?) and then use that variable name as where I'm inserting from? I thought maybe using the cmd.CommandText "SQL Statement" in ADO.

    I haven't tried this, but was hoping someone has done something like this before.

    Sarah

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Insert Into from Union? (2000)

    Again, no. I am very sorry.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Insert Into from Union? (2000)

    Clever!

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: SQL Insert Into from Union? (2000)

    As HansV noted, there is no straightforward way to do this if not using saved queries. However, you can use a slightly convoluted workaround, as shown in this example. This uses an ADO recordset and ADO GetRows method to insert the records returned by Union query into another table:

    Public Sub InsertUnionQuery()
    On Error GoTo Err_Handler

    Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim intCount As Integer
    Dim n As Integer
    Dim varData As Variant ' Array variable
    Dim strMsg As String
    Dim strSQL As String

    Set rst = New ADODB.Recordset
    Set cnn = CurrentProject.Connection

    ' Note: Field1 is Text, Field2 is Number, Field3 is Date

    strSQL = "SELECT Table1.Field1, Table1.Field2, Table1.Field3 " & _
    "FROM Table1 " & _
    "UNION SELECT Table2.Field1, Table2.Field2, Table2.Field3 " & _
    "FROM Table2 ORDER BY Field1;"

    rst.Open strSQL, cnn, adOpenStatic, adLockOptimistic
    intCount = rst.RecordCount

    ' GetRows method: 1st subscript identifies = field; 2nd = record number
    If intCount > 0 Then
    varData = rst.GetRows(intCount)
    For n = 0 To intCount - 1
    strSQL = "INSERT INTO Table3 (Field1, Field2, Field3) " & _
    "VALUES ('" & varData(0, n) & "', " & varData(1, n) & ", #" & varData(2, n) & "#);"
    cnn.Execute strSQL
    Next n
    Else
    MsgBox "No records returned by query.", vbExclamation, "NO RECORDS"
    End If

    rst.Close

    Exit_Sub:
    If Not IsEmpty(varData) Then Erase varData
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
    Err_Handler:
    Select Case Err.Number
    Case 0
    Resume Next
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strMsg, vbExclamation, "INSERT UNION QUERY ERROR"
    Resume Exit_Sub
    End Select

    End Sub

    The GetRows method copies the records returned by a recordset into a 2-dimensional array. The first subscript identifies the field and the second identifies the record number. You can then loop thru this array and insert the values into a table using the SQL INSERT... VALUES syntax that allows you to add one record at a time with specified values. I've attached a small sample db (A2K format) to demonstrate this technique; run sub above, and all records from Table1 & Table2 will be appended to Table3. For demo purposes the 3 fields (Field1, etc) in each table are Text, Number, and Date data types respectively - note delimiters in SQL INSERT statement. For more info look up GetRows method (ADO) in VB Help and INSERT INTO statement in Jet SQL Reference Help.

    HTH
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL Insert Into from Union? (2000)

    There is another way of doing it that works well in Access 2000 and later. It worked sometimes in 97, but not the way it does in the VB6-based versions. The idea is to use the union query as an in-line subquery and create a join to that subquery. Here's an example that doesn't really make sense but shows the technique: It creates a union on a customer table like that in Northwind with a non-existent "All Customers" record, then joins that union query to an Order table and creates a temporary table of the results.

    SELECT CID.CustomerID, O.OrderID, O.OrderDate, O.ShippedDate
    INTO TempOrders
    FROM tblOrders AS O
    RIGHT JOIN
    <font color=red>[</font color=red>SELECT C.CustomerID, C.CompanyName
    FROM tblCustomers AS C
    UNION Select "<All>", "<All Customers>" FROM tblCustomers AS C1<font color=red>].</font color=red> AS CID

    ON O.CustomerID = CID.CustomerID
    WHERE (((O.OrderDate) Is Not Null));

    Note the square brackets around the in-line subquery followed by a period.
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Insert Into from Union? (2000)

    Mark,

    Thanks for the advice. It got it to work except for one last name that has an apostrophe in it. I get a syntax error when it tries to insert that value into the field.

    I'm sure I need to put brackets or parens around something, but not sure what. Here is my code. The LastName value is inserted at column 4.

    strSQLAppend = "INSERT INTO tblPossibleMembers (ID, EmployeeNbr, FirstName, FamilarName, LastName) " & _
    "VALUES ('" & varData(0, n) & "', " & varData(1, n) & ", '" & varData(2, n) & "','" & varData(3, n) & "','" & varData(4, n) & "');"

    I tried putting parens around varData(4,n) but that didn't work.

    Sarah

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Insert Into from Union? (2000)

    Hi Sarah,

    I am glad that Mark and Charlotte have found solutions for you.

    If you have a value with an apostrophe in it, but no values that contain double quotes, you can replace the single quotes around varDaya(4, n) by double quote, but in order to insert double quotes into a quoted string, you must double them, i.e. use double double quotes (getting dizzy yet?)

    strSQLAppend = "INSERT INTO tblPossibleMembers (ID, EmployeeNbr, FirstName, FamilarName, LastName) " & _
    "VALUES ('" & varData(0, n) & "', " & varData(1, n) & ", '" & varData(2, n) & "','" & varData(3, n) & "',""" & varData(4, n) & """);"

  10. #10
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Insert Into from Union? (2000)

    Thanks Hans, the extra double quotes worked.

    So that got MarkD's solution to work, but I'm inserting 577 rows into the table and it takes a little over a minute to complete. So that solution is acceptable, but not ideal. I don't know if Charlotte's solution is any better, but I'll give it a try.

    Sarah

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: SQL Insert Into from Union? (2000)

    If you're appending that number of records then loading the results of a recordset into an array & looping thru it probably isn't the most efficient approach. Here is a dumb question: Is there some reason you just can't run separate append queries (in code) for each table used in union query? Simple example:

    Public Sub RunAppendQuery()
    On Error GoTo Err_Handler

    Dim cnn As ADODB.Connection
    Dim lngRecords As Long
    Dim strMsg As String
    Dim strSQL As String

    Set cnn = CurrentProject.Connection

    strSQL = "INSERT INTO Table3 ( Field1, Field2, Field3 )" & _
    "SELECT Table1.Field1, Table1.Field2, Table1.Field3 " & _
    "FROM Table1;"

    cnn.Execute strSQL, lngRecords

    MsgBox CStr(lngRecords) & " records were appended to destination table.", vbInformation, "RECORDS APPENDED"

    strSQL = "INSERT INTO Table3 ( Field1, Field2, Field3 )" & _
    "SELECT Table2.Field1, Table2.Field2, Table2.Field3 " & _
    "FROM Table2;"

    lngRecords = 0
    cnn.Execute strSQL, lngRecords

    MsgBox CStr(lngRecords) & " additional records were appended to destination table.", vbInformation, "RECORDS APPENDED"

    Exit_Sub:
    Set cnn = Nothing
    Exit Sub
    Err_Handler:
    Select Case Err.Number
    Case 0
    Resume Next
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "RUN APPEND QUERY ERROR"
    Resume Exit_Sub
    End Select
    End Sub

    This code appends all records from Table1 & Table2 to Table3. This is simple example, the SQL used to append records can be modified to meet any criteria necessary. If the union query is being used as a means to weed out duplicate records in the two source tables, indexes can be created on destination table to serve same purpose. Or is there something I'm missing here?

    HTH

  12. #12
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Insert Into from Union? (2000)

    Nope you're not missing anything.

    In fact, that approach completely slipped my mind. After struggling with Charlotte's approach and getting help from one of my co-workers, I came to the conclusion that, " <img src=/S/doh.gif border=0 alt=doh width=15 height=15> Duh, why don't I do it in 2 separate sql statements!?"

    So yes, that's the way I'm doing it now. And it's working much faster than your original approach, Mark. (Although I did learn something in doing that approach, so that time wasn't wasted. ) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Sarah

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL Insert Into from Union? (2000)

    In-line subqueries can be very confusing and frustrating until you get the hang of them. Then they become one of the first things you think of because the in-line subquery is the equivalent of a join to another saved query but without having to save the other query separately.
    Charlotte

Posting Permissions

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