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();
}

WHERE ? >= 0
[download]

I am not sure this will actually work. The DBI will quote each parameter passed with a placeholder. This is a column name, not a value.

What is the meaning of ...WHERE" . $_ . " >= 0") then? Aren't comparing the column name there too?

Arjen

I mean, can you basically get T-SQL to do all the work so that no Perl script would be required?

Hmm. After some Googling an clicking I found out that T-SQL is Transact-SQL, which is an SQL extension on MS SQL Server and Sybase. You learn something new every day :-)

In response to your question, I'd have to say “probably”, as it's described as a full-featured language. Maybe Transact-SQL Programming and/or Transact-SQL Cookbook can be of help.

Arjen

A quick hack (in Sybase's T-SQL dialect) to get you on your way:

declare @sex int, @start int, @end int
declare @column varchar(32)
declare @sql varchar(255)

-- First you need to insert the column names into a temp table:
create table #col (colname varchar(32))
insert #col(colname) values('Male_00_04')
insert #col(colname) values('Female_00_04')
-- etc...

select @start = 0, @end = 4, @sex = 1

declare col_cr cursor for select colname from #col
open col_cr
fetch col_cr into @column
while(@@sqlstatus = 0)
begin
    select @sql = "INSERT INTO Adjusted_census_data_normalised 
     (GeographicCode
     , Sex
     , Age_start
     , Age_end
     , Person_count)
   select A.GeographicCode
        , " + convert(varchar, @sex) 
        + ", " + convert(varchar, @start) 
        + ", " + convert(varchar, @end) 
        + ", A." + @column + "
    FROM        Adjusted_census_data A 
   WHERE       " + @column + " >= 0"

    exec (@sql)
    
    if (@sex = 1)
        select @sex = 2
    else
        select @sex = 1
    if (@sex = 1)
    begin
        select @start = @start +5
        if (@start > 84)
          select @end = 115
        else
          select @end = @end+5
    end

    fetch col_cr into @column
end

close col_cr
deallocate cursor col_cr
[download]