MySQL stored procedure to tidy up 2016 census tables

census_2016_normalise_table.sql

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