as Onno says, it is usually better to use the Moodle API (i.e. the $DB object and the functions in "course/lib.php") to manipulate the Moodle database, even if you are not using the Moodle user-interface.
For example if you want to add a new course module record, you must remember to also add a "context" record, and to insert the new course module id in the appropriate place of the "sequence" field of the the appropriate "course_sections" record. Phew!
However, if you are determined to do it without the Moodle API, then all you need to do is run an SQL "insert" query without the "id" field set. The id will be set automtically for you (by the database). You can find out what it was set to, if you use the appropriate command for your database. For example, for MySQL you would use the "LAST_INSERT_ID()" command. If you are programming in PHP, you can use PHP's "mysql_insert_id()" function.