Last active
July 17, 2017 05:10
-
-
Save jaketclarke/db36060746dc5d7609e6418e0cb8f645 to your computer and use it in GitHub Desktop.
MySQL stored procedure to tidy up 2016 census tables
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| BEGIN | |
| declare db varchar(100); | |
| declare dbnew varchar(100); | |
| declare geog varchar(5); | |
| declare tbl varchar(100); | |
| declare tblnew varchar(100); | |
| declare keycolumn varchar(20); | |
| declare keytest int; | |
| declare keytype varchar(20); | |
| set db = 'census_2016'; | |
| set dbnew = 'census_2016_mod'; | |
| # in case someone wants to modify this for a different geog later, this isn't hard coded below | |
| set geog = 'AUS'; | |
| # if no type bit, it's auswide, set = aus | |
| if type is null then set type = 'AUS'; | |
| end if; | |
| # build var representing db.table, so i only have to write it once | |
| # if its geog-wide, don't append the type var | |
| if type = 'AUS' then | |
| set tbl = CONCAT(db,'.2016Census_',number,'_',geog); | |
| set tblnew = CONCAT(dbnew,'.2016Census_',number,'_',geog); | |
| else | |
| set tbl = CONCAT(db,'.2016Census_',number,'_',geog,'_',type); | |
| set tblnew = CONCAT(dbnew,'.2016Census_',number,'_',geog,'_',type); | |
| end if; | |
| # build table | |
| SET @s = CONCAT('DROP TABLE IF EXISTS ',tblnew); | |
| PREPARE stmt FROM @s; | |
| EXECUTE stmt; | |
| DEALLOCATE PREPARE stmt; | |
| SET @s = CONCAT('CREATE TABLE ',tblnew,' LIKE ',tbl); | |
| PREPARE stmt FROM @s; | |
| EXECUTE stmt; | |
| DEALLOCATE PREPARE stmt; | |
| # insert data into table | |
| SET @s = CONCAT('INSERT INTO ',tblnew,' SELECT * FROM ',tbl); | |
| PREPARE stmt FROM @s; | |
| EXECUTE stmt; | |
| DEALLOCATE PREPARE stmt; | |
| # whats the name of the key column? | |
| if type = 'SA1' then | |
| set keycolumn = 'SA1_7DIGITCODE_2016'; | |
| elseif type = 'SA2' then | |
| set keycolumn = 'SA2_MAINCODE_2016'; | |
| else | |
| SET keycolumn = concat(type,'_CODE_2016'); | |
| end if; | |
| # in how many rows does the key column contain any chars? | |
| # if any column type = varchar | |
| set @s = concat( | |
| 'SELECT count(*) INTO @keytest FROM ',tbl, ' WHERE ', keycolumn, ' REGEXP \'[A-Za-z]\'' | |
| ); | |
| PREPARE stmt FROM @s; | |
| EXECUTE stmt; | |
| DEALLOCATE PREPARE stmt; | |
| # if one or more rows contain a char, then varchar, else int | |
| if @keytest > 0 then set keytype = 'varchar(20)'; | |
| else set keytype = 'int(11)'; | |
| end if; | |
| SET @s = CONCAT('ALTER TABLE ',tblnew,' change column ',keycolumn,' CODE_2016 ', keytype ,' not null primary key'); | |
| PREPARE stmt FROM @s; | |
| EXECUTE stmt; | |
| DEALLOCATE PREPARE stmt; | |
| END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment