Currently I have a SQL statement that will generate an answer set of INSERT statements.
I want to write a stored procedure that will first run my initial SQL with a variable for databasename, after inputting the databasename it will perform SQL that will generate the SQL for the insert statements (lots of them).
I then want it to run all of those individual INSERT statements so they load into a hard coded table.
Any help would be great.
Of course it's possible within an SP, but "lots of INSERT statements" are probably the wrong way to handle it.
Are there multiple source/target tables for the INSERTs?
Could you share your existing logic to create those INSERTs?
Ok, it's different source tables for each INSERT, so there's no way to optimize the number of INSERTs.
But you should change some syntax:
The row count should be calculated without DatabaseName etc:
CAST(COUNT(*) AS DECIMAL(18,0))
The same for the count per value, the outer most select should add 'DatabaseName, TableName, ColumnName'
Finally the percentage calculation should be simplified to:
TRIM(100 * Y/X)
Then it's just a CURSOR using this SELECT and a "CALL DBC.SysExecSQL(:variable_containing_Insert_statement);" for each row.
Btw, if you don't need fully detailed information info about row count and the number of rows for the most common values can also be extracted using HELP STATS when stats have been collected
Check the FOR syntax and the rules for dynamic SQL.
Depending on your TD release you can use dbc.SysExecSQL or EXECUTE IMMEDIATE to dynamically submit the Inserts.
FOR cur AS
SELECT 'INSERT INTO UDWETLSANDBOX.PRFL_COL_DOMAIN '||
'GROUP BY 1,2,3,4) AS X (XDB,XTB,XCL,XCDV,XNV,XPERV,XTS);' AS str
FROM DBC.COLUMNS A
WHERE A.DATABASENAME = 'UDW1ETLSRCREP'
AND A.COLUMNNAME LIKE '%CD%'
CALL dbc.SysExecSQL(cur.str) ;
This is a SP to collect row counts from all tables within a database, it's very basic, no error checking etc.
But it shows a cursor and dynamic SQL using dbc.SysExecSQL or EXECUTE IMMEDIATE:
CREATE SET TABLE RowCounts
DatabaseName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
TableName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
PRIMARY INDEX ( DatabaseName ,TableName )
REPLACE PROCEDURE GetRowCounts(IN DBName VARCHAR(30))
DECLARE SqlTxt VARCHAR(500);
FOR cur AS
TRIM(DatabaseName) AS DBName,
TRIM(TableName) AS TabName
WHERE DatabaseName = :DBName
AND TableKind = 'T'
SET SqlTxt =
'INSERT INTO RowCounts ' ||
'SELECT ' ||
'''' || cur.DBName || '''' || ',' ||
'''' || cur.TabName || '''' || ',' ||
'count(*)' || ',' ||
'CURRENT_TIMESTAMP(0) ' ||
'FROM ' || cur.DBName ||
'.' || cur.TabName || ';';
-- EXECUTE IMMEDIATE sqlTxt;
Thanks again! I tried running this after configuring it to line up with what I am doing but I am having an issue actually calling the procedure once its created.
I limited because of a SELECT WITH GRANT OPTION access problem, is this because the database I made the SP in does not have access, or because personally I do not have access?