Shell script to update flatfile DMRId.dat to MySQL Database

Add below crontab :-

20 3 * * * /usr/bin/curl --fail -o /tmp/DMRIds.dat -s http://www.pistar.uk/downloads/DMRIds.dat > /dev/null 2>&1

/path/update_newestdmrid_datfromtmp.sh :-

#!/bin/bash
/usr/bin/mysql --user=dbuser --password='password' -e "source /path/update_newestdmrid_datfromtmp.sql"

/path/update_newestdmrid_datfromtmp.sql :-

USE table;

CREATE TABLE IF NOT EXISTS temp_table SELECT ccs7, callsign, name FROM table LIMIT 0;

CREATE INDEX ccs7 on temp_table (ccs7) USING BTREE;
CREATE INDEX callsign on temp_table (callsign) USING BTREE;
CREATE INDEX name on temp_table (name) USING BTREE;

LOAD DATA LOCAL INFILE '/tmp/DMRIds.dat'
INTO TABLE temp_table
FIELDS TERMINATED BY '\t';

INSERT INTO table
(ccs7, callsign, name)
SELECT t1.ccs7, t1.callsign, t1.name FROM temp_table t1
WHERE NOT EXISTS(SELECT ccs7 FROM table t2
WHERE t2.ccs7 = t1.ccs7 and t2.callsign = t1.callsign);

DROP TABLE temp_table;

***remark your dbuser,password,table & temp_table to your db/user and table in MySQL database

Leave a Reply

Your email address will not be published.

*