I have a project beyond my SQL skill level I need help with.
I have some number of temporary tables I import into SQL Server via Server Management Studio where the table names begin with "temp".
I execute a script that transfers the tempTable records to SQL tables of the same name - less the "temp". For example, tempClient inserts records into dbo.Client.
Once that script executes, I'd like to verify records transferred correctly by running another script listing only those SQL tables and their row count.
The following script lists only the tempTables and row count but that's not what I'm looking for. I need the SQL tables and row count where the SQL table names are the same name as the tempTable names. Otherwise, I have to review hundreds of tables in the dB to locate and verify the row count of a few.
t.name AS TABLENAME, p.[Rows]
FROM sys.tables t
INNER JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.[rows]>0 AND t.name LIKE '%temp%'
ORDER BY t.name