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();

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();