How to write an Insert Select Statement?

Question

This Method for creating a subquery using JDatabase provides some great insight and I think it would be beneficial to have a agreed method of subquery documented. I came across this because I am wanting to do the same thing but as an insert statement. The closest I ever got was replicated the entire table because for some reason I keep losing the columns.

$query = $db->getQuery(true);
$pre = $db->getQuery(true);

// Insert columns.
$columns = array('user_id', 'title', 'ordering', 'published', 'description');

$now = $query->currentTimestamp();
// Insert values.

$pre->insert($db->qn('#__table'))->columns($db->qn($columns));


// Prepare the insert query.
$query->select(array(
                    $db->q('') . ' AS id',
                    $db->qn('user_id'),
                    $db->qn('c.name', 'title'),
                    $db->q('1') . ' AS published',
                    $db->q('') . ' AS ordering',
                    $db->q('') . ' AS created_by',
                    $now .  ' AS created_on',
                    $db->q('') . ' AS modified_by',
                    $db->q('') . ' AS modified_on',
                    )
            )
            ->from($db->qn('#__other_table', 'c'));

    $query = $pre . $query ;

    // Set the query using our newly populated query object and execute it.
    $db->setQuery($query);

    $db->execute();

Answer 1

Although this is an old question, it might be helpful to others. Here is the insert by select query using JDatabase.

Build some select query using JDatabase

$querySelect = $db->getQuery(true)
  ->select('id, title')
  ->from('sometable');

Now your insert query

$queryInsert = $db->getQuery(true)
  ->insert('anothertable')
  ->columns($db->qn(array('id','title')))
  ->values($querySelect)

Hope this helps.

Answer 2

You have to type which values you want to insert

$pre->insert($db->qn('#__table'))->columns($db->qn($columns))->values(here values);