Return to Question

I understand the drupal structure allows more power, and from reading this post about content types and database seems I would be crazy to expect to create a database table for my lessons and exercises. So then, how would I create SQL queries in drupal to perform the following task?

I understand the drupal structure allows more power, and from reading this post about content types and database seems I would be crazy to expect to create a database table for my lessons and exercises. So then, how would I create SQL queries in drupal to perform the following task?

EDIT3: while the below worked, I ended up following the advice in the answers and used the API according to this post about adding nodes programmatically and this one which specifically talks about adding nodes with Entity Reference field. I then manually updated the node_counter table because I wasn't sure how to do that via the API.

$pnuke = db_query("SELECT * FROM nuke_guitarlessons_guitarlessons WHERE 1");

$cids = array(8,10,11,13,14); // ignore these lesson categories
$catmap=array(); // this maps old cat ids to the taxonomy term ids
$catmap[] = 7;$catmap[] = 0;$catmap[] = 1;$catmap[] = 9;$catmap[] = 6;$catmap[] = 2;$catmap[] = 8;$catmap[] = 0;$catmap[] = 0;$catmap[] = 0;$catmap[] = 0;$catmap[] = 10;

db_set_active();

$lastid=4; // this just ensures that node ids are incremented as I want
while ($n = $pnuke->fetchAssoc()) {
    if (!in_array($n['cid'], $cids)) {
    $datets = strtotime($n['date']);
    $title = $n['title'];
 
    db_insert('node')
        ->fields(array('vid' => $lastid+1, 'type' => 'lesson', 'language' => 'und', 'title' => $title, 'uid' => 1, 'status' => 1, 'created' => $datets, 'changed' => $datets, 'comment' => 0, 'promote' => 1, 'sticky' => 0, 'tnid' => 0, 'translate' => 0))
        ->execute();
 
    db_insert('field_data_body')
        ->fields(array('entity_type' => 'node', 'bundle' => 'lesson', 'deleted' => 0, 'entity_id' => $lastid+1, 'revision_id' => $lastid+1, 'language' => 'und', 'delta' => 0, 'body_value' => $n['introtext'], 'body_summary' => '', 'body_format' => 'full_html'))
        ->execute();

    db_insert('field_data_field_lessonintro')
        ->fields(array('entity_type' => 'node', 'bundle' => 'lesson', 'deleted' => 0, 'entity_id' => $lastid+1, 'revision_id' => $lastid+1, 'language' => 'und', 'delta' => 0, 'field_lessonintro_value' => $n['description'], 'field_lessonintro_format' => 'full_html'))
        ->execute();
 
    db_insert('taxonomy_index')
        ->fields(array('nid' => $last_id+1, 'tid' => $catmap[(int)($n['cid']-1)], 'sticky' => 0, 'created' => $datets))
        ->execute();
    
    db_insert('field_data_field_category')
        ->fields(array('entity_type' => 'node', 'bundle' => 'lesson', 'deleted' => 0, 'entity_id' => $lastid+1, 'revision_id' => $lastid+1, 'language' => 'und', 'delta' => 0, 'field_category_tid' => $catmap[(int)($n['cid']-1)]))
        ->execute();    
    
 $lastid++;
  }
}

EDIT2: I was missing some tables, so below is everything I did to import. Things seem to work but I have no idea if there is some other obscure setting I missed...

$pnuke = db_query("SELECT * FROM nuke_guitarlessons_guitarlessons WHERE 1");

$cids = array(8,10,11,13,14); // ignore these lesson categories
$catmap=array(); // this maps old cat ids to the taxonomy term ids
$catmap[] = 7;$catmap[] = 0;$catmap[] = 1;$catmap[] = 9;$catmap[] = 6;$catmap[] = 2;$catmap[] = 8;$catmap[] = 0;$catmap[] = 0;$catmap[] = 0;$catmap[] = 0;$catmap[] = 10;

db_set_active();

$lastid=4; // this just ensures that node ids are incremented as I want
while ($n = $pnuke->fetchAssoc()) {
    if (!in_array($n['cid'], $cids)) {
    $datets = strtotime($n['date']);
    $datenow = new DateTime();
 
    $title = $n['title'];
 
    db_insert('node')
    ->fields(array('vid' => $lastid+1, 'type' => 'lesson', 'language' => 'und', 'title' => $title, 'uid' => 1, 'status' => 1, 'created' => $datets, 'changed' => $datets, 'comment' => 0, 'promote' => 1, 'sticky' => 0, 'tnid' => 0, 'translate' => 0))
    ->execute();
 
    db_insert('node_revision')
    ->fields(array('nid' => $lastid+1, 'vid' => $lastid+1, 'uid' => 1, 'title' => $title, 'log' => '', 'timestamp' => $datenow->getTimestamp(), 'status' => 1, 'comment' => 0, 'promote' => 1, 'sticky' => 0))
    ->execute();
 
    db_insert('field_data_body')
    ->fields(array('entity_type' => 'node', 'bundle' => 'lesson', 'deleted' => 0, 'entity_id' => $lastid+1, 'revision_id' => $lastid+1, 'language' => 'und', 'delta' => 0, 'body_value' => $n['introtext'], 'body_summary' => '', 'body_format' => 'full_html'))
    ->execute();

    db_insert('field_revision_body')
    ->fields(array('entity_type' => 'node', 'bundle' => 'lesson', 'deleted' => 0, 'entity_id' => $lastid+1, 'revision_id' => $lastid+1, 'language' => 'und', 'delta' => 0, 'body_value' => $n['introtext'], 'body_summary' => '', 'body_format' => 'full_html'))
     ->execute();

    db_insert('field_data_field_lessonintro')
    ->fields(array('entity_type' => 'node', 'bundle' => 'lesson', 'deleted' => 0, 'entity_id' => $lastid+1, 'revision_id' => $lastid+1, 'language' => 'und', 'delta' => 0, 'field_lessonintro_value' => $n['description'], 'field_lessonintro_format' => 'full_html'))
    ->execute();
    
    db_insert('field_revision_field_lessonintro')
    ->fields(array('entity_type' => 'node', 'bundle' => 'lesson', 'deleted' => 0, 'entity_id' => $lastid+1, 'revision_id' => $lastid+1, 'language' => 'und', 'delta' => 0, 'field_lessonintro_value' => $n['description'], 'field_lessonintro_format' => 'full_html'))
    ->execute();
    
    db_insert('node_counter')
    ->fields(array('nid' => $lastid+1, 'totalcount' => (int)$n['hits'], 'daycount' => 1, 'timestamp' => $datenow->getTimestamp()))
    ->execute();
 
    db_insert('taxonomy_index')
    ->fields(array('nid' => $lastid+1, 'tid' => $catmap[(int)($n['cid']-1)], 'sticky' => 0, 'created' => $datets))
    ->execute();
    
    db_insert('field_data_field_category')
    ->fields(array('entity_type' => 'node', 'bundle' => 'lesson', 'deleted' => 0, 'entity_id' => $lastid+1, 'revision_id' => $lastid+1, 'language' => 'und', 'delta' => 0, 'field_category_tid' => $catmap[(int)($n['cid']-1)]))
    ->execute();
    
    db_insert('field_revision_field_category')
    ->fields(array('entity_type' => 'node', 'bundle' => 'lesson', 'deleted' => 0, 'entity_id' => $lastid+1, 'revision_id' => $lastid+1, 'language' => 'und', 'delta' => 0, 'field_category_tid' => $catmap[(int)($n['cid']-1)]))
    ->execute();
    
 $lastid++;
  }
}