Re: Interpolating subroutine call in SQL INSERT INTO SELECT statement

[poj]

Which database ?. This is for MySQL. Note user must have the correct permissions to create functions. The function is simply a demo, obviously you don't need a function to concat something to a field.

#!perl
use strict; 
use warnings; 
use DBI; 
use Data::Dump 'pp';

my $dbh = get_dbh();

my $proc = "
  CREATE FUNCTION my_subrt(s CHAR(30))
    RETURNS CHAR(50)
    RETURN CONCAT(s,'####');
  ";
$dbh->do('DROP FUNCTION IF EXISTS my_subrt'); 
$dbh->do($proc); 
#$dbh->do('DELETE FROM TABLE2'); 
my $sql = '
  INSERT INTO TABLE2 (COLUMN1, COLUMN2, COLUMN3)
  SELECT COLUMN1, COLUMN2, my_subrt(COLUMN3)
  FROM TABLE1 
  WHERE COLUMN4=?';
my $rv  = $dbh->do($sql, {}, 'foo');

my $ar = $dbh->selectall_arrayref('SELECT * FROM TABLE2');
pp $ar;

$dbh->do('DROP FUNCTION my_subrt'); 

sub get_dbh{
  my $database = "test"; my $user = ""; my $pw = "";
  my $dsn = "dbi:mysql:$database:localhost:3306"; 
  my $dbh = DBI->connect($dsn, $user, $pw, { 
         RaiseError=>1, AutoCommit=>1 } );
  return $dbh;
}

poj

[shadowsong]

@poj

Although I decided to split up the statement and process the insert separately - with values from the select, I will definitely be trying this out later on one of my other projects.

Thanks!

[poj]

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

[shadowsong]

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!