UNIX for Beginners Questions & Answers

<<BOF>>
record_number|id_number|first name|last name|msisdn|network|points|card number|gender
312|9101011234011|Test Junior|Smith|071 123 4321|MTN|73|1241551413214444|M
313|9012023213011|Bob|Smith|27743334321|Vodacom|3|1231233232323244|M
314|8706055678011|Frank|Frankinson|2771 156 1567|8ta|0|1231123453214444|M
315|9102078765011|Mary|Van Niekerk|+27(0)711236677|CellC|2|1278933213214444|F

316|9005074545011|Susan|Wilson|0821121124|CellC|705|1231233216544444|F
317|9101013232011|Katherine|Jeeves|+271233214|8ta|112|1231233678214444|F
318|9101011234011|Matthew|Matthias|0711111111|MTN||1231555213214444|M
319|9103126666011|Michael|Bay|085-6122-161|8ta|63|1231244413214444|M
320|7506023232300|Tyrone|Olivier|711234322|CellC|89|1234563213214444|M
321|8901020304055|Burt|Jackson|071 4566544|Vodacom|1|4567233213214444|M
<<EOF>>

CREATE TABLE `tUSER` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`id_number` varchar(20) NOT NULL,
`first_names` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
INDEX(`id_number`,`first_names`,`last_name`)
);
CREATE TABLE `tPROFILE` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`tUSER_id` bigint(20) DEFAULT NULL,
`tTYPES_id` bigint(20) DEFAULT NULL,
`value` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
INDEX(`tUSER_id`,`tTYPES_id`,`value`)
);
CREATE TABLE `tTYPES` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`type` varchar(100) NOT NULL DEFAULT '',
`description` varchar(255) NOT NULL,
`deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
INDEX(`type`,`description`,`deleted`)
);

CREATE TEMPORARY TABLE tmp_import (`record_number` INT(10) NOT NULL PRIMARY KEY , 
id_number VARCHAR(50), `first name` VARCHAR(255) , `last name` VARCHAR(255) ,
 msisdn VARCHAR(50) , network VARCHAR(20) , points INT ,
`card number` VARCHAR(100) , gender VARCHAR(1));

LOAD DATA LOCAL INFILE 'path/to/dsv' 
INTO TABLE tmp_import 
FIELDS TERMINATED BY '|' 
LINES TERMINATED BY '\n';

DELETE FROM tmp_import WHERE record_number = 0;

-- select * from tmp_import;

INSERT INTO tUSER (id_number ,first_names,last_name)
SELECT id_number, `first name`, `last name`
FROM   tmp_import;

INSERT INTO tTYPES (`type` ,cellphone , description)
SELECT network, `msisdn`, `gender`
FROM   tmp_import;