May 31, 2018 at 3:00 pm
Hi,
I have a table that includes other tables name
create table #temptable
(
TableName nvarchar(50),
RecordCount int
)
insert into #temptable values('Table1' ,65417);
insert into #temptable values('Table2' ,116177);
insert into #temptable values('Table3' ,49732);
insert into #temptable values('Table4' ,30);
insert into #temptable values('Table5',4);
insert into #temptable values('Table6' ,969775);
insert into #temptable values('Table7',25946);
insert into #temptable values('Table8' ,163);
insert into #temptable values('Table9' ,2994);
insert into #temptable values('Table10' ,3109576);
insert into #temptable values('Table11' ,1752744);
insert into #temptable values('Table12' ,3);
insert into #temptable values('Table13' ,9);
insert into #temptable values('Table14' ,1677)
;
I need to create a procedure that will check the amount from my #temptable against a count from that table (we get the number from another process).
Right now I have a check that does it table by table, but I would like to have a dynamic query as the number of tables will grow with time (right now I have 32 tables.
I have tried, but I am not sure how to fix what I am doing wrong. And I know some things are not right.
Here is what I wrote
DECLARE @i INT = 1
DECLARE @number INT
DECLARE @SQL VARCHAR(4000)=''
DECLARE @TableName NVARCHAR(50)
DECLARE @TSQL INT
DECLARE @mssql INT
SET @number = (SELECT COUNT(1) FROM LoadCount)
While @i <= @number
BEGIN
Select @TableName = TableName from LoadCount where NumberID = @i
SET @TSQL = (SELECT RecordCount from LoadCount where TableName = @TableName)
SET @mssql = (SELECT COUNT(1) FROM mydatabase.dbo. @TableName)
SET @i = @i + 1
END
SET @SQL=
'
IF @TSQL = @mssql
insert into #TempTable (DateChecked, TableName, OracleCount, MSSQLCount, Status) values (@GETDATE, ''' + @TableName + ''', @TSQL, @mssql, ''Pass'')
ELSE
insert into #TempTable (DateChecked, TableName, OracleCount, MSSQLCount, Status) values (@GETDATE, ''' + @TableName + ''', @TSQL,@MSSQL, ''Fail'')
'
EXECUTE(@SQL)
May 31, 2018 at 3:45 pm
SELECT t.TableName
, t.RecordCount
FROM #temptable t
JOIN sys.tables t2 ON t.TableName=t2.name
JOIN
(
SELECT object_id, Sum(rows) rows
FROM sys.partitions
GROUP BY object_id
) p ON t2.object_id=p.object_id
WHERE p.rows <> t.RecordCount
June 1, 2018 at 6:52 am
Thanks, but that is not what I am looking for.
I made a mistake on my sample also, I am trying to get a count into a new table.
Basically I count the rows before I import data from Oracle, and I want to compare it to the number of final rows that were imported into sql server.
I have a table that gives me the table name and the number of rows that came from oracle.
then I need to compare it, to the number of rows in the sql server (table by table)
DECLARE @i INT = 1
DECLARE @number INT
DECLARE @SQL VARCHAR(4000)=''
DECLARE @TableName NVARCHAR(50)
DECLARE @TSQL INT
DECLARE @mssql INT
SET @number = (SELECT COUNT(1) FROM LoadCount)
While @i <= @number
BEGIN
Select @TableName = TableName from LoadCount where NumberID = @i
SET @TSQL = (SELECT RecordCount from LoadCount where TableName = @TableName)
SET @mssql = (SELECT COUNT(1) FROM mydatabase.dbo. @TableName)
SET @i = @i + 1
END
SET @SQL=
'
IF @TSQL = @mssql
insert into #TempTableOne (DateChecked, TableName, OracleCount, MSSQLCount, Status) values (@GETDATE, ''' + @TableName + ''', @TSQL, @mssql, ''Pass'')
ELSE
insert into #TempTableOne (DateChecked, TableName, OracleCount, MSSQLCount, Status) values (@GETDATE, ''' + @TableName + ''', @TSQL,@MSSQL, ''Fail'')
'
EXECUTE(@SQL)
and the table will look like this
create table #TempTableOne
(
DateChecked datetime,
TableName nvarchar (50),
OracleCount int
MSSQLCount int,
Status nvarchar (5)
)
June 1, 2018 at 7:11 am
astrid 69000 - Friday, June 1, 2018 6:52 AMThanks, but that is not what I am looking for.
I made a mistake on my sample also, I am trying to get a count into a new table.Basically I count the rows before I import data from Oracle, and I want to compare it to the number of final rows that were imported into sql server.
I have a table that gives me the table name and the number of rows that came from oracle.
then I need to compare it, to the number of rows in the sql server (table by table)
DECLARE @i INT = 1
DECLARE @number INT
DECLARE @SQL VARCHAR(4000)=''
DECLARE @TableName NVARCHAR(50)
DECLARE @TSQL INT
DECLARE @mssql INT
SET @number = (SELECT COUNT(1) FROM LoadCount)While @i <= @number
BEGINSelect @TableName = TableName from LoadCount where NumberID = @i
SET @TSQL = (SELECT RecordCount from LoadCount where TableName = @TableName)
SET @mssql = (SELECT COUNT(1) FROM mydatabase.dbo. @TableName)SET @i = @i + 1
END
SET @SQL=
'
IF @TSQL = @mssql
insert into #TempTableOne (DateChecked, TableName, OracleCount, MSSQLCount, Status) values (@GETDATE, ''' + @TableName + ''', @TSQL, @mssql, ''Pass'')
ELSE
insert into #TempTableOne (DateChecked, TableName, OracleCount, MSSQLCount, Status) values (@GETDATE, ''' + @TableName + ''', @TSQL,@MSSQL, ''Fail'')
'EXECUTE(@SQL)
and the table will look like this
create table #TempTableOne
(
DateChecked datetime,
TableName nvarchar (50),
OracleCount int
MSSQLCount int,
Status nvarchar (5)
)
Joe's solution is almost what you want. You just need to do an UPDATE instead of a SELECT. Give it a try, and post back if you're still struggling.
John
June 1, 2018 at 8:00 am
Thanks so much to both of you. I took apart Joe's query to understand it and I made it work as John suggested.
The only thing is that the row count that comes from the partitions, you need to choose indexes otherwise you get duplicated rows.
and also I work with schemas therefore I need to take part of the table name I had on my table with the list.
here is what i did.
create table #TempTableOne
(
DateChecked datetime default getdate(),
TableName nvarchar (50),
OracleCount int,
MSSQLCount int,
Status nvarchar (5)
)
insert into #TempTableOne (TableName, OracleCount, MSSQLCount, Status)
SELECT t.TableName, t.RecordCount as OracleCount, p.rows as MSSQLCount,
case when t.RecordCount = p.rows then 'Pass' else 'Fail' end as [Status]
FROM LoadCount t
JOIN sys.tables t2 ON SUBSTRING(t.TableName, 5, 100)=t2.name
JOIN
(
SELECT object_id, Sum(rows) rows
FROM sys.partitions
where index_id = 0
GROUP BY object_id
) p ON t2.object_id=p.object_id
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply