Fun with SQL

If you want to put one thing into a database, that's easy.

 INSERT INTO table_1 (field_1 , field_2 , field_3 ) VALUES ( 4 , 1 , 2 ) ; 

I've been putting lots of things into table_1 that are in table_2, where there are n instances of table_1 in each table_2. This lead me to something like this.

 
my $sql ;
$sql = << 'SQL'
SELECT id FROM table_2 WHERE field_3 = ?
SQL
my $ptr = db_arrayref( $sql , $param->{ field_3 } ) ;

$sql = << 'SQL'
INSERT INTO table_1 ( 
    field_1 , field_2 , field_3 
    ) 
VALUES (
    ? , ? , ?
    )
SQL

map {
   my @vals ;
   push @vals , $param->{ a } ;
   push @vals , $param->{ b } ;
   push @vals , $_ ;
    }  @$ptr ;

That's OK for what it is, but what that ends up meaning is lots of small SQL commands sent to the server. SQL handles the commands well, but the opening and closing of network connections is just sort of sucky, so, that's not optimal. So, if you can just send one command, that's what you want. And in this case, the command is this:

 
INSERT INTO table_1 ( field_1 , field_2 , field_3 ) 
    SELECT 
    1 ,
    2 ,
    field_3 FROM table_2 WHERE field_3 = ?
    ; 

Isn't SQL wonderful?