Insert dynamic results into table

Question

Post reply

Insert dynamic results into table

Saran

Right there with Babe

Points: 760

November 15, 2023 at 2:17 am

#4327687

Hi All,

I would like to insert the 2nd statement to a table. If I copy and paste to SSMS and run it is working.

Is it possible to automatically run this, without copy and paste results into SSMS?

create table #tbl_sysobjects_row_count (name sysname,row_count int)

select 'insert into #tbl_sysobjects_row_count select '''+name+''', count(*) [row_count] from ['+name+']' from sys.objects where type ='u'

--insert into #tbl_sysobjects_row_count select 'tbl_123', count(*) [row_count] from [tbl_123]

select * from #tbl_sysobjects_row_count

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

So all you want to do is copy the table name and the number of records into a table? Why not query the system tables? Sort of like this:

SELECT ao.name, ao.object_id, p.rows
FROM sys.all_objects ao
INNER JOIN sys.partitions p
ON ao.object_id = p.object_id
WHERE ao.type_desc LIKE 'USER_TABLE'
AND ao.is_ms_shipped = 0
AND ao.name != 'sysdiagrams';

Saran Right there with Babe Points: 760 More actions · Answer 2

Thanks for the update. I am just trying to compare replication tables row count from publisher to subscriber. Thought of dynamically generate rowcount for all replicated tables both side and compare it from stored proc.

I have tried this https://www.sqlservercentral.com/articles/using-t-sql-to-verify-tables-row-counts-in-transactional-replication but it is not working correctly.

Ed B Hall of Fame Points: 3669 More actions · Answer 3

You could use sp_MSforeachtable to avoid writing a loop, but it will only work in the database it's run from, so you couldn't query two databases at the same time to compare them.

EXEC sp_MSforeachtable 'INSERT #tbl_sysobjects_row_count SELECT ''?'' , Count(*)  FROM ?'