Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Re^3: Interpolating subroutine call in SQL INSERT INTO SELECT statement

by poj (Abbot)
on Aug 26, 2015 at 19:57 UTC ( #1140111=note: print w/replies, xml ) Need Help??


in reply to Re^2: Interpolating subroutine call in SQL INSERT INTO SELECT statement
in thread Interpolating subroutine call in SQL INSERT INTO SELECT statement

Depending on your volume of data, in the past I have used the bulk loader in MSSQL to insert many records at once. It does require the load file to be accessible by the server. One advantage is you can check your function is working as expected before changing the database.

#!perl use strict; use DBI; my $dbh = dbh(); my $sql = 'SELECT COLUMN1,COLUMN2,COLUMN3 FROM TABLE1 WHERE COLUMN4 = ?'; my $sth = $dbh->prepare($sql); $sth->execute('foo'); my $tmpfile = "c:\\temp\\public\\temp1.dat"; open TMP,'>',$tmpfile or die "$!"; while (my @f = $sth->fetchrow_array){ $f[2] = subrt($f[2]); print TMP (join "\t",@f)."\n"; } close TMP; my $rv = $dbh->do('DELETE FROM TABLE2'); print "$rv records deleted from TABLE2\n"; $rv = $dbh->do( " BULK INSERT TABLE2 FROM '$tmpfile' WITH ( FIELDTERMINATOR = '\t' )" ); print "$rv records insert into TABLE2"; sub subrt { reverse shift } # connect sub dbh { my $dsn = "DBI:ODBC:mssql"; my $dbh = DBI->connect($dsn, 'sa', '', {RaiseError => 1, PrintError => 1}) or die (Error connecting " $DBI::errstr"); }
poj
  • Comment on Re^3: Interpolating subroutine call in SQL INSERT INTO SELECT statement
  • Download Code

Replies are listed 'Best First'.
Re^4: Interpolating subroutine call in SQL INSERT INTO SELECT statement
by shadowsong (Monk) on Aug 27, 2015 at 09:02 UTC

    Very nice...

    Although in this instance the server is an MSSQL installment on a separate machine (so the load file isn't accessible)

    1. I can still use this technique to check my function works as expected, and
    2. I have another project which ought to benefit immensely from this technique!

    Thanks for that poj!

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1140111]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (10)
As of 2019-06-24 20:30 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Is there a future for codeless software?



    Results (99 votes). Check out past polls.

    Notices?