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) . ')';

[download]

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

[download]

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.

-- Ken

In reply to Re: perl mysql - INSERT INTO, 157 columns by kcott
in thread perl mysql - INSERT INTO, 157 columns by ler224

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.