Re: perl mysql - INSERT INTO, 157 columns

[kcott]

G'day ler224,

Don't hand-code all 1,333 characters of that string!

Think of the maintainance nightmare you're creating. Are you, or the next poor sod who takes over from you, going to physically count 157 '?' characters to make sure there's the right number? How were you intending to check there were exactly 157 colN elements and that N represents an unbroken sequence of unique integers from 1 to 157?

You already have a tool in front of you to assign the correct string. Use it.

my $query = 'INSERT INTO table (' . join(',' => map { "col$_" } 1 .. $
+cols)
            . ') VALUES (' . join(',' => ('?') x $cols) . ')';

Also, dynamically generate the value of $cols so that you don't have to edit the code if the number of columns changes. You said "My data is currently 157 columns.": this suggests to me that previously it was some other value or there's a potential for that number to change in the future. You don't show enough code for me to say how that might be best implemented; perhaps something like:

my $cols = @{ $csv->getline($fh) };

Finally, your column names (i.e. "col1" .. "col157") are a very poor choice. How are you planning to reference them? Are you planning to rewrite all your SQL (or, at least, review all your SQL to see whether rewrites are required) whenever columns are added/removed to/from your CSV file? I don't know enough about your data to advise you further; however, if your CSV data (or its source) has column headings, that would be a good place to start.