Let's say I don't happen to have the ability to create a Stored Procedure, but I have to run the same query on a regular basis. Would I be able to put my query in a .txt document and be able to run it from Powershell by calling it? What syntax would I use for myquery.txt to run? Put another way, what's a way to end-run not being able to use a stored procedure on a query that should be made into one?
Executing a script via Powershell possibility
netezzapowershellstored-procedures
Related Solutions
I tend to shy away from the FEDERATED storage engine for three(3) reasons:
- It only supports MyISAM
- DDL performed on the source table requires manually changing the FEDERATED table design on external services.
- Bulk operations against a FEDERATED table can become an instant mightmare !!!
What to do ???
One possibility is to use mysqldump and merge them
EXAMPLE
Assuming all tables have identical structures
Assuming all tables are not using auto_increment IDs
Assuming all tables are InnoDB
On the server that will combine the three tables, create four(4) import databases (one time operation):
CREATE DATABASE ImportDB1;
CREATE DATABASE ImportDB2;
CREATE DATABASE ImportDB3;
CREATE DATABASE ImportCombined;
Import Process Goes Something Like This
mysqldump the three(3) tables
mysqldump -hHOSTIP1 -Pportnumber1 -u... -p... dbname tbname > /root/ImportTable1.sql &
mysqldump -hHOSTIP2 -Pportnumber2 -u... -p... dbname tbname > /root/ImportTable2.sql &
mysqldump -hHOSTIP3 -Pportnumber3 -u... -p... dbname tbname > /root/ImportTable3.sql &
wait
Load the mysqldumps into the separate import databases in parallel:
mysql -hHOSTIP4 -u... -p... -A -DImportDB1 < /root/ImportTable1.sql &
mysql -hHOSTIP4 -u... -p... -A -DImportDB2 < /root/ImportTable2.sql &
mysql -hHOSTIP4 -u... -p... -A -DImportDB3 < /root/ImportTable3.sql &
wait
rm -f /root/ImportTable1.sql &
rm -f /root/ImportTable2.sql &
rm -f /root/ImportTable3.sql &
wait
Next, combine the data.
DROP TABLE IF EXISTS ImportCombined.tbname;
CREATE TABLE ImportCombined.tbname LIKE ImportDB1.tbname;
ALTER TABLE ImportCombined.tbname DISABLE KEYS;
INSERT INTO ImportCombined.tbname SELECT * FROM ImportDB1.tbname;
INSERT INTO ImportCombined.tbname SELECT * FROM ImportDB2.tbname;
INSERT INTO ImportCombined.tbname SELECT * FROM ImportDB3.tbname;
ALTER TABLE ImportCombined.tbname ENABLE KEYS;
ALTER TABLE ImportCombined.tbname ENABLE KEYS;
Now, perform the task you already have in place against the ImportCombined.tbname table.
"You fill in these steps"
Then, when the table is ready to be exported, mysqldump it and copy 2 times:
mysqldump -hHOSTIP4 -Pportnumber4 -u... -p... ImportCombined tbname > /root/ExportTable1.sql
cp /root/ExportTable1.sql /root/ExportTable2.sql &
cp /root/ExportTable1.sql /root/ExportTable3.sql &
mysql -hHOSTIP1 -Pportnumber1 -u... -p... -A -Ddbname < /root/ExportTable1.sql &
mysql -hHOSTIP2 -Pportnumber2 -u... -p... -A -Ddbname < /root/ExportTable2.sql &
mysql -hHOSTIP3 -Pportnumber3 -u... -p... -A -Ddbname < /root/ExportTable3.sql &
wait
rm -f /root/ExportTable[123].sql &
As show from these steps, these things should be shell scripted and perhaps crontab'd.
Here is another maneuver: If the tables are MyISAM, you could copy the MyISAM tables (.frm, .MYD, and .MYI files for each table) in other folders (databases in the eyes of mysqld) instead of doing mysqldumps, and perform similar operations as mentioned before. Make sure no writes to the tables take place during the copy.
What is funny about temporary tables in a stored procedure is not so much the transient existence of the table (which gets dropped upon the DB connection's termination), but the scope of the stored procedure.
Someone asked this question on StackOverflow : Scope of temp tables created in MySQL stored procedure. It has been over a year and nobody answered the question? Let me set the record straight. The fact is: The temp table exists inside and outside of the Stored Procedure, but you can do things with the temporary table only inside the scope of a running Stored Procedure.
According to the Book
Chapter 5 has a subheading Returning Result Sets to Another Stored Procedure.
It says in paragraph 2 on Page 117:
Unfortunately, the only way to pass a result set from one stored procedure to another is to pass the results via a temporary table. This is an awkward solution b, and -- because the temporary table has scope throughout the entire session -- it creates many of the same maintainability issues raised by the use of global variables. but if one stored program needs to supply another stored program with results, then a temporary table can be the best solution.
Looking back at the StackOverflow question, I can see someone called the Stored Procedure from the mysql client. Since the mysql client is not a Stored Procedure, the results cannot be manipulated the mysql client level via DML other than doing a SELECT to see the results. Since you calling a recursive stored procedure, you can rest assured the temp table is fully accessible for the duration of the DB Connection.
I hope this answers your question.
UPDATE 2014-01-31 11:26 EST
In your last comment, you said
If we employ persistent connections, will the MEMORY table persist through multiple REQUESTS, and it seems it will, so for performance sake, I'm assuming that using this method will *REQUIRE us to explicitly DROP the temporary MEMORY table. Do I assume correctly?
Yes and No. I say Yes because it is one way to do it. I say no because another way to do it is:
CREATE TEMPORARY TABLE IF NOT EXISTS id_list (iid CHAR(32) NOT NULL) ENGINE=memory;
TRUNCATE TABLE id_list;
Whichever way you choose, the operation is still the same since TRUNCATE TABLE drops and recreates the table. This will not harm other DB Connections since each Connection has its own id_list table.
Related Question
- Sql-server – SQL Server CREATE and DROP TABLE Permissions in Stored Procedure Only
- Sql-server – Using Powershell Variables in a File-Stored Query with SQL Server
- Stop, Start, or Restart a SSAS Tabular service instance in PowerShell with Invoke-Command and $variables
- Sql-server – How to create a row for every day in a date range using a stored procedure
- Sql-server – Running Powershell Code embedded in SSIS or TSQL, not in external file
- Sql-server – Executing SQL Script through PowerShell gets inconsistent results vs in SSMS
Best Answer
It depends on your Powershell versions but you have a few options. I tend to use several of them based on the scenario but in your case, why not just call a
SQLCMD.EXE
from your powershell Script? It has the most compatibility, it's less buggy and more compatible thanInvoke-SQLCMD
, and outside ofDSC
I haven't seen MS useosql
. It would look something like this:Otherwise you'd have to install the SQL Server feature pack from v12 and above, then use
Invoke-SQLCMD
which has some compatibility issues and doesn't provide too many benefits except in certain cases I've found. You also get full SMO functionality with the Feature Pack but it's overkill and has too much config overhead for a simple query.