Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2014-07-22 : 14:50:01
|
I have a view created from only one table.VW_Stats ( Tab_Name,Load_Status,User,....)Tab_Name & Load_Status columns provides the information about Name of table and status of data getting loaded to this table.To this I would like to add a column to display the running count in the table, I mean how many records so far loaded, as below the recordCount coming from the same table tbl_collectionTable_name Load_Status RecordCount User...tbl_collection Running 1244 XYZ |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-22 : 14:56:51
|
You can't do it in the view, so you'll need to calculate it separately and then add the value to your result set.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2014-07-22 : 15:12:54
|
A function call to get the count(*) by passing the table name as a parameter?-Neil |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2014-07-22 : 16:51:00
|
I am sorry, I've problem with my chrome & Ie, they are not allowing me to post 2-3 lines messages. I think I got the rt hint on this from Tkizer, to use function by passing the table name to get the result.-Neil |
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2014-07-23 : 06:10:36
|
I have a view created from only one table.VW_Stats ( Tab_Name,Load_Status,User,....)Tab_Name & Load_Status columns provides the information about Name of table and status of data getting loaded to this table,The Stats table from which the view is created, is used for capturing the current data load stats, the data is being loaded in to several tables from source, I am trying to get the number of records loaded into each table during the data load still running.Select * from Vw_StatsTable_name Load_Status User...tbl_collection Running XYZTo this I would like to add a column to display the running count in the table, I mean how many records so far loaded, as below the recordCount coming from the same table tbl_collectionTable_name Load_Status RecordCount User...tbl_collection Running 1244 XYZis this possible?create view vw_stats(Table_name,Load_status,(Select count(*) from Table_name) as RecordCount, -- I would like to know how to make this workUser,from tbl_stats-Neil |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-07-23 : 12:00:52
|
If you wanted you can query the DMVs. There are certain levels of permissions that you might need. Here is a link that describes several ways to get a row count for a table and the permissions needed:http://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/Here is a sample of how you might go about putting it in a view:-- Create table to testCREATE TABLE tbl_collection (id int);INSERT tbl_collection VALUES (1), (2), (3);CREATE TABLE tbl_stats (Table_name VARCHAR(128));INSERT tbl_stats VALUES ('tbl_collection');-- Main QuerySELECT o.name, ddps.row_count FROM sys.indexes AS iINNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_IDINNER JOIN tbl_stats AS tbl ON o.name = tbl.Table_name INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id WHERE i.index_id < 2 AND o.is_ms_shipped = 0; -- CleanupDROP TABLE tbl_collection;DROP TABLE tbl_stats; NOTE: It'd probably be a good idea to add the Table Schema as a separate column to your tbl_stats table to help minimize issues in the future. |
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2014-07-23 : 15:31:22
|
Thanks Lamprey, I'll try this-Neil |
|
|
|
|
|
|
|