Insert statement using Select *

Hi,

Not sure why this is getting me stuck but it is.

I have a pivot query in access that obvisouly creates columns based on the pivot, hence the column names can change. So I was trying to insert the contents of the pivot query into a table like so.

INSERT INTO tbl_output ( Column1, column2, column3, column4, column5)
SELECT * FROM qry_pivot

However I keep getting the error

"The INSERT INTO statement contains the following unknown field name: 'sAge_item'. Make sure you have typed the name correctly, and try the operation again.
LVL 4
gardmanITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as the error message indicates, you specified a column somewhere that is not correct.
please triple-check the column names...
0
 
MilleniumaireCommented:
When you list the columns to be inserted into, it is also best if you list the columns in the select statement.  This way, if someone adds a new column to the qry_pivot table, the insert .. select statement will still work.  Try listing the columns, this may also solve your problem:

INSERT INTO tbl_output ( Column1, column2, column3, column4, column5)
SELECT c1, c2, c3, c4, c5 FROM qry_pivot
0
 
mbizupCommented:
As a3 said, the field names are mismatched.

< hence the column names can change.>
My understanding is that you are unable to predict the column names in the source query.

Since you are using a pivot table, a VBA solution might be needed to handle the changing column names by aliasing them to the fieldnames in the destination table:


Dim rs as DAO.recordset
Dim strSQL as string
Set rs = currentDB.openrecordset ("qryPivot", dbopendynaset)
 
strSQL = "INSERT INTO tbl_output ( Column1, column2, column3, column4, column5) "
strSQL = strSQL & " SELECT " & rs.fields(0).Name & " AS column1, "  &  rs.fields(1).Name &  " AS column2, "  &  rs.fields(2).Name & " as Column3, "  &  rs.fields(3).Name & " AS column4, "  &  rs.fields(4).Name & "  As Column5 FROM qryPivot "
 
Currentdb.Execute strSQL, dbfailonerror
rs.close
set rs = nothing

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
gardmanITAuthor Commented:
@angelll

The column name the error is refering to is in the source pivot query which is referenced usign the "SELECT * FROM qry_pivot" Hence this cannot be the case.

@Milleniumaire

I cannot list the columns I am select as they are built from a pivot query.

So where say the data before the pivot is
sage_item | sage_value
a101           10
a102           12
a103           20

The column names would be

A101, a102, a103

As expected from a pivot query and hence why i was using select * from rather than explicitly naming the columns.

@mbizup

A solution that would seem to be the next viable step.. however I cannot understand why a simple insert into with a select * from is not working and why it would decide to error refering to a column name from the source table when im not even specifying the source tables columns.

Is there something I dont know about that prevents you using select * from when inserting.. why would be beyond me??

Source pivot table has the following structure
sAge_Item
sAge_Period_1 <-whatever the retured value is
sAge_Period_2 <-whatever the retured value is
sAge_Period_3 <-whatever the retured value is

*as said before its a pivot query hence the column names will actually be named as the periods that appear in the table that the query is built from.

Target table has the following structure
Item_code
Period_1
Period_2
Period_3

0
 
mbizupCommented:
<I dont know about that prevents you using select * from when inserting>

Using a wildcard to select 'all' field names, you need exact fieldname matches between the source and destination.

If you spell out the exact fieldnames from the source table or query, you do not need exact matches, just the correct number of columns.  In fact the aliases in my own code may be overkill.  The overall purpose of the code is to allow you to select unknown fieldnames by number.

I couldn't tell you why, but that's the way it is.
0
 
gardmanITAuthor Commented:
Dynamically building the fields and the columns to insert into was the way to go with this.

Not only did I have the problem of the column names being unpredictable I also ran into the problem of the number of columns being unpredicatable.

So what I did was create a table that I would populate with the three different pivot tables that produced the data I was interested in. I set a hard limit of columns to be produce by each of the 3 pivot queries then used code to either bulk out the insert statement with null columns or clip the extra columns found... in my case this was fine as I was never after more than 18 months of data so if i got 19 I could ignroe the 19th column. if I got 17 columns I would get the 18th column with nothing in it which was the best solution.

Thanks to all for the suggestions, I feel mbizup hit the nail on the head as far as having a solution that did not direclty fix my original question but was the next logical solution. I then expanded on that by looping thorugh to get the fields as said above.

THanks

Antony
Function insert_data()
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim xField, xCountC, x, xConcat, SetColumnsC, SetColumnsF, SetColumnsV, xCountF, xCountV
    
    
    'clear out the destination table
    CurrentDb.Execute ("Delete from tbl_output_Ageing")
    
    'Build the field destination list
    xField = "Item_code , Description, QTY, Volume, [Product Group], [Value], Store, [Intro Date], [Parent Code], UOM, [Oldest Stock], [Due Out], " & _
                "[Current Period QTY] , [Current Period QTY - 1], [Current Period QTY - 2], [Current Period QTY - 3], [Current Period QTY - 4], " & _
                "[Current Period QTY - 5], [Current Period QTY - 6], [Current Period QTY - 7], [Current Period QTY - 8], [Current Period QTY - 9], " & _
                "[Current Period QTY - 10], [Current Period QTY - 11], [Current Period QTY - 12], [Current Period QTY - 13], [Current Period QTY - 14], " & _
                "[Current Period QTY - 15], [Current Period QTY - 16], [Current Period QTY - 17], [Current Period QTY - 18], [10701], [0], [Item Code_1], " & _
                "STORE_1, [Forecast Period], [Forecast Period 1], [Forecast Period 2], [Forecast Period 3], [Forecast Period 4], [Forecast Period 5], " & _
                "[Forecast Period 6], [Forecast Period 7], [Forecast Period 8], [Forecast Period 9], [Forecast Period 10], [Forecast Period 11], qry_pivot_the_value_sAge_Item, " & _
                "qry_pivot_the_value_DITMD, qry_pivot_the_value_QTY, qry_pivot_the_value_MUDN3, qry_pivot_the_value_GIGRP1, qry_pivot_the_value_£SDSV, qry_pivot_the_value_ESTOR3, " & _
                "qry_pivot_the_value_MUDA2, qry_pivot_the_value_MUDA3, qry_pivot_the_value_UOMTU, qry_pivot_the_value_AgePeriod, [qry_pivot_the_value_Due Out], [Oldest Period Value], " & _
                "[Current Period Value], [Current Period Value - 1], [Current Period Value - 2], [Current Period Value - 3], [Current Period Value - 4], [Current Period Value - 5], " & _
                "[Current Period Value - 6], [Current Period Value - 7], [Current Period Value - 8], [Current Period Value - 9], [Current Period Value - 10], [Current Period Value - 11], " & _
                "[Current Period Value - 12], [Current Period Value - 13], [Current Period Value - 14], [Current Period Value - 15], [Current Period Value - 16], [Current Period Value - 17], [Current Period Value - 18], V10701, V0 "
    
    
    
    'Set the amount of columns that we are working on for the Current
    SetColumnsC = 33
    'Set the amount of columns that we are working on for the Current
    SetColumnsF = 14
    'Set the amount of columns that we are working on for the Current
    SetColumnsV = 34
    
    
    
    'Get column count from the seperate tables(used only for working out columns) -2 for the two crap fields at the end
    xCountC = FieldExists2("qry_pivot_the_shizzle") - 2
    
    'Get column count from the seperate tables(used only for working out columns)
    xCountF = FieldExists2("qry_Pivot_Forecast") - 1
    
    'Get column count from the seperate tables(used only for working out columns) -2 for the two crap fields at the end
    xCountV = FieldExists2("qry_pivot_the_value") - 2
    
    'build the first part of the insert statement with the fields generated from above
    strSQL = "INSERT INTO tbl_output_Ageing ( " & xField & ") "
    
    '=============================================
    'Start the process for each of the 3 queries
    '=============================================
    '1-Query for Current
    '==========================================================
    
    'Query the individual query to get the columns
    Set rs = CurrentDb.OpenRecordset("qry_pivot_the_shizzle", dbOpenDynaset)
             
    'Set our starting point to 0
    x = 0
    
    Do While x < xCountC
        xConcat = xConcat & "[qry_pivot_the_shizzle." & rs.Fields(x).Name & "]"
        xConcat = xConcat & ","
        x = x + 1
    Loop
    
    Do While SetColumnsC > x
        xConcat = xConcat & "[0],"
        x = x + 1
    Loop
    rs.Close
    
    '2-Query for Forecast
    '==========================================================
    'Query the individual query to get the columns
    Set rs = CurrentDb.OpenRecordset("qry_Pivot_Forecast", dbOpenDynaset)
             
    'Set our starting point to 0
    x = 0
    
    Do While x < xCountF + 1
        'MsgBox rs.Fields(x).Name & " x=" & x
        xConcat = xConcat & "[" & rs.Fields(x).Name & "]"
        xConcat = xConcat & ","
        x = x + 1
    Loop
    
    Do While SetColumnsF > x
        xConcat = xConcat & "[0],"
        x = x + 1
    Loop
    rs.Close
    '----------------------------------------------------------
    
    '3-Query for Value
    '==========================================================
    'Query the individual query to get the columns
    Set rs = CurrentDb.OpenRecordset("qry_pivot_the_value", dbOpenDynaset)
             
    'Set our starting point to 0
    x = 0
    
    Do While x < xCountV
        xConcat = xConcat & "[qry_pivot_the_value." & rs.Fields(x).Name & "]"
        xConcat = xConcat & ","
        x = x + 1
    Loop
    
    Do While SetColumnsV > x
        xConcat = xConcat & "[0],"
        x = x + 1
    Loop
    rs.Close
    '----------------------------------------------------------
    
    'Trim the "," of the end
    xConcat = Left(xConcat, Len(xConcat) - 1)
    
    strSQL = strSQL & " SELECT " & xConcat & " FROM qry_pivot_combined "
    'Debug.Print xConcat
    Debug.Print strSQL
    'Debug.Print xField
    'Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError
    Set rs = Nothing
End Function

Open in new window

0
 
gardmanITAuthor Commented:
Please see above final post for more info on my acceptance of the solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.