Multiple statement handlers with single DB handler for temporary table

Sarat1729 has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, I have a requirement to work on. It is connect to a SQL DB (MS SQL) and query the data using a join with a temporary table. Basically it involves a SQL to create temporary table and insert data into (using select into) and another to query data with a join on this with already existing table. Can I execute these two queries using a single DB handler, since I cannot access temporary table in a different session by creating another DB handler. If so, will it have any performance implications. Any other suggestions that can be made to meet the requirement. Thanks in Advance...

Comment on Multiple statement handlers with single DB handler for temporary table

Replies are listed 'Best First'.
Re: Multiple statement handlers with single DB handler for temporary table by Corion (Patriarch) on Jan 10, 2018 at 08:02 UTC
Yes, you can send multiple statements through the same DBI database handle. For loading the temporary table, if it is large I suggest that you investigate what bulk loading mechanisms your database offers. Other than that, there are no performance suggestions or implications that I see.	[reply]
Re: Multiple statement handlers with single DB handler for temporary table by thanos1983 (Parson) on Jan 10, 2018 at 09:41 UTC
Hello Sarat1729, Some time ago I wrote a sample of script in (MySQL syntax) that should do the majority of the steps that you want to do. MySQL and SQL the code is 90% - 95% the same minor modification would be needed. To avoid code duplication find sample of code here Re^7: extract column data. If I understood correctly from your question you want to drop the table after retrieving the data, if this is the case you can simply do: `sub drop { my ($dbh, $table_name) = @_; my $quoted_name = $dbh->quote_identifier($table_name); $dbh->do("DROP TABLE $quoted_name"); }` [download] The code has been copied from DBI "drop table" from the forum. Hope this helps, BR. Seeking for Perl wisdom...on the process of learning...not there...yet!	[reply] [d/l] [select]