Oracle UNION SELECT from Excel Worksheet: UnionSelectRange() Function

One thing that data wranglers will have to do from time to time is take a bunch of values from an Excel spreadsheet and plug them into an Oracle query. One approach is to use the concatenate() function with a dizzying number of single and double quotes. To make this process much simpler, here is a custom VBA function called UnionSelectRange that handles the work:


Usage:

Generated UNION SELECT sql statement:

select 'adf' as A1, 'dfa' as B1, 'ad' as C1 from dual
union select 'sdddd' as A1, 'af' as B1, 'd' as C1 from dual
union select 'adf' as A1, 'dfa' as B1, '' as C1 from dual
union select 'fa' as A1, '' as B1, 'af' as C1 from dual
union select 'd' as A1, 'adf' as B1, '' as C1 from dual
union select '' as A1, '' as B1, 'adf' as C1 from dual
union select 'df' as A1, 'a' as B1, 'f' as C1 from dual

Limitations:
* Headers are assumed to exist - row 1 is used for column aliasing
* The function expects a single contiguous range of data. Example: you can't pass in something like =UnionSelectRange(A2:D2, F2)
* If the selected range is one row, each row gets a 'union'. You'll have to manually remove the union from the first row
* If the selected range is multiple rows, Excel will put double quotes around the copied union select. Simply remove them to use the SQL.
* Oracle limitations - maximum number of selected columns is 1000, maximum column alias length is 30 characters

Source:

Public Function UnionSelectRange(dataRange As Range) As String
    Dim st As String
    
    Dim x As Long, y As Long
    
    For x = 1 To dataRange.Rows.Count
        If x > 1 Then
            st = st & Chr(13) & Chr(10) & "union "
        End If
        
        'Add a union if doing a row at a time
        If dataRange.Rows.Count = 1 Then
            st = st & "union "
        End If
        
        st = st & "select "
        
        For y = 1 To dataRange.Columns.Count
            If y > 1 Then
                st = st & ", "
            End If
            st = st & "'" & dataRange.Cells(x, y).Value & "' as " & Sheets(dataRange.Parent.Name).Cells(1, dataRange.Cells(x, y).Column).Value
        Next y
        
        st = st & " from dual"
    Next x
    
    UnionSelectRange = st
End Function