HI,
I NEED TO DO A ROWCOUNT ON ALL TABLES ON A GIVEN DATABASE. I CAN EASILY DO THIS IN SQL USING A PIVOT TABLE.
CAN SOMEONE HELP?
THIS IS WHAT I HAVE:
LETS SAY THIS IS (TABLE_COUNTS)
TABLENAMES ROWCOUNTS
TABLE1 500
TABLE2 200
TABLE3 800
TABLE4 499
THIS IS WHAT I WANT TO ACCOMPLISH:
LETS SAY THIS IS (TABLE_INPUTDATA)
TABLE1 TABLE2 TABLE3 TABLE4
500 200 800 499
CAN SOMEONE HELP PLEASE. LET ME KNOW IF YOU NEED ADDITIONAL INFORMATION. THANKS.
I found my own solution.
It's good that you found a solution on your own, but it would be nice if you could share it.
Btw, your question is quite confusing, i still don't know what you actually wanted to do.
Dieter
Hi Dieter,
I have a few TD tables that I needed to do rowcounts. e.g.
count(*) from TableName.
Now, the result of the count is going to a temp table. Once I get all the counts inserted into the temp table (temptable) then I need to convert those rows to column. Just like using pivot in SQL server.
The way I accomplished this task was using the following syntax:
Select
Max(Case when t2.tablename in('table1') then rowsnum else null end) table1
,Max(Case when t2.tablename in('table2') then rowsnum else null end) table2
,Max(Case when t2.tablename in('table3') then rowsnum else null end) table3
from
(select tablename as tablename, rowsnum, row_number() over(order by tablename) as RowNr from temptable)
t2
Now, the result of this query get inserted into another permanent table that i can use to compare the counts.
Now my table read:
databaseName table1 table2 table3
Raymond 100 200 300
Raymon2 100 200 300
See, the counts match.
In the event that the count doesn't match, i get notification. This process it to load a production table from dev.
Thankx,
Raymond V.
Hi Raymond,
what if number of tables increases and you finally hit the 2048 columns in a table limit :-)
For me it would be much easier to compare those counts when they are rows in a table.
Dieter
Thank you Deiter.
I do understand, but I need to keep my counts in a single row for all the tables for history audits.
The ideal solution is if i can convert the following MSSQL server code to Teradata:
SET NOCOUNT ON;
CREATE TABLE #temp(
[table1] [int] NULL,
[table2] [int] NULL,
[table3] [int] NULL,
[table4] [int] NULL
) ON [PRIMARY]
INSERT #temp
SELECT
[table1] ,
[table2] ,
[table3] ,
[table4]
FROM (
SELECT so.name [TableName], si.rows [RowCount]
-- SELECT *
FROM sysobjects so
INNER JOIN sysindexes si on si.id = OBJECT_ID(so.name)
AND SI.INDID <2
WHERE so.xtype = 'U'
) base
PIVOT (MAX([RowCount]) FOR
[TableName] IN (
[table1] ,
[table2] ,
[table3] ,
[table4]
)) piv
Sorry, I couldn't put everything in one post.
From here I just insert to Count_History from #temp
There's no place like sysindexes in Teradata which keeps the rowcount.
You need to run multplie SELECTs like
SELECT
(SELECT COUNT(*) FROM tab1) as cnt1,
(SELECT COUNT(*) FROM tab2) as cnt2,
...
Or you extract tha info from the stats, it's not exact, but if stats are mantained regularily it might be good enough.
Dieter