INSERT with SELECT query.

[goodguy]

I have a SELECT query whose results I need to INSERT into another table. Currently I have made the query an Insert query but this makes testing and development difficult. Most times I just need to browse the records returned without inserting them as they already exist in the target table. I have kept a separate copy of the query as SELECT but keeping them both updated is also demanding. Is it possible to use an INSERT statement with Currentdb.Execute and pass the SELECT query as an argument/parameter instead of individual records?

[stmoong]

You can create a parameterised query for the SELECT. Then, in your code, you do something like this:

    'rsNew, rsAdd are DAO.RecordSet
    'qdfAdd is DAO.QueryDef
 
    Set qdfAdd = db.QueryDefs("YourParameterisedQuery")
    qdfAdd.Parameters!param1 = param1Value
    Set rsAdd = qdfAdd.OpenRecordset()
    rsAdd.MoveFirst
 
    Set rsNew = db.OpenRecordset("YourTableName")
    rsNew.AddNew
    rsNew!field1 = rsAdd!field1
    rsNew!field2 = rsAdd!field2
    rsNew!field3 = rsAdd!field3
    .....
    rsNew.Update

[maximus]

This is a small example of how to use Select with Insert Into used with currentdb.execute.

Dim strSQL As String
strSQL = "INSERT INTO Table3 ( EX_1, Ticket ) SELECT Table2.EX_1, Table2.Ticket FROM Table2 WHERE (((Table2.Ticket)=2));"
CurrentDb.Execute strSQL, dbFailOnError

or 

Dim strSQL As String
strSQL = "Insert Into Table3(EX_1,Ticket)Select Table2.EX_1,Table2.Ticket From Table2 Where Ticket=2"
CurrentDb.Execute strSQL, dbFailOnError

[goodguy]

Thank you both for your responses; however, please note that both your approaches are row-based, i.e. they process one row at a time. I need a set-based solution, one which does not need to loop through a recordset.

[orange]

Thank you both for your responses; however, please note that both your approaches are row-based, i.e. they process one row at a time. I need a set-based solution, one which does not need to loop through a recordset.

stmoong has given a recordset approach
maximus is sql based

What do you have in mind for "set based"? I would suggest that maximus has given a set approach.

[stmoong]

If you're looking to insert multiple records at once, then you may use the SQL approach suggested by maximus.

Also, reference from MSDN:
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Actually, if you need to browse the data to be inserted during development, you can just click on the datasheet view, and not the Run button. This is same for the Delete.

[goodguy]

Thanks, all of you.
Maximus' solution can be set-based WITHOUT the WHERE clause.
The Microsoft link on INSERT INTO syntax says that multiple records can be appended to a target table provided they are coming from a tableexpression i.e. one or more tables. I am appending results of a query to a table. Seems query is not supported.

Syntax

Multiple-record append query:
INSERT INTO target [(field1[, field2[, …]])] [IN externaldatabase] SELECT [source.]field1[, field2[, …] FROM tableexpression
The INSERT INTO statement has these parts: PartDescription targetThe name of the table or query to append records to. field1, field2Names of the fields to append data to, if following a target argument, or the names of fields to obtain data from, if following a source argument. externaldatabaseThe path to an external database. For a description of the path, see the IN clause. sourceThe name of the table or query to copy records from. tableexpressionThe name of the table or tables from which records are inserted. This argument can be a single table name or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN operation or a saved query.

[stmoong]

From what I understand, the WHERE clauses in the SELECT query suggested by maximus is used to return a set of filtered records. So, in effect, it allows you to insert a set of filtered records.

Also, you can use a SELECT * FROM some complicated query with join, as the documentation states that tableexpression is "The name of the table or tables from which records are inserted. This argument can be a single table name or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN operation or a saved query."

[goodguy]

OK, will try.