By “T-SQL”, I assume that you want to insert the column names in one “blow” (or transaction :-). See the RaiseError and AutoCommit options to DBI::connect in the DBI documentation.

This could be a way (changed the interpolated strings to placeholders):

#!/usr/bin/perl

use strict;
use warnings;

my @columns = qw/Male_00_04 Female_00_04 Male_05_09 Female_05_09 Male_10_14
                 Female_10_14 Male_15_19 Female_15_19 Male_20_24 Female_20_24
                 Male_25_29 Female_25_29 Male_30_34 Female_30_34 Male_35_39
                 Female_35_39 Male_40_44 Female_40_44 Male_45_49 Female_45_49
                 Male_50_54 Female_50_54 Male_55_59 Female_55_59 Male_60_64
                 Female_60_64 Male_65_69 Female_65_69 Male_70_74 Female_70_74
                 Male_75_79 Female_75_79 Male_80_84 Female_80_84 Male_85_115
                 Female_85_115/;

my @columns_B = @columns;

my $start = 0;
my $end = 4;
my $sex = 1;

# Connect to the database with the parameter "AutoCommit" set to 0,
# and RaiseError to 1!

my $sthB_A = $dbh->prepare(qq/
    INSERT INTO Adjusted_census_data_normalised
                (GeographicCode, Sex, Age_start, Age_end, Person_count) (
                 SELECT A.GeographicCode, ?, ?, ?, ?
                 FROM Adjusted_census_data A WHERE ? >= 0)
"/) or die "Couldn't prepare query: ".$dbh->errstr;

eval {
    foreach my $col (@columns) {
        $sthB_A->execute($sex, $start, $end, "A$col", $col);
        if ($sex == 1) {
            $sex = 1;
        } else {
            $sex = 2;
        }
        if ($sex == 1) {
            $start += 5;
            if ($start > 84) {
                $end = 115;
            } else {
                $end += 5;
            }
        }
    }
};
$dbh->commit();
if ($@) {
    warn "Cannot process $col: $@\n";
    $dbh->rollback();
}