locked
create table with timestamp column RRS feed

  • Question

  • Hi.

    Im trying to insert the values from this query into a table, so I can later check the history of memory usage:

    SELECT 
    [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
        ,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
        ,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
        ,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
        ,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
        ,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
        ,[system_memory_state_desc] AS [System_Memory_State_Desc]
    	
    FROM [master].[sys].[dm_os_sys_memory]

    What I'm missing is a way to insert the current timestamp every time I insert to the table.

    Is there an easy way to do that?

    My plan is to use the insert into command.

    Thursday, July 2, 2015 10:06 AM

Answers

  • If you are just looking to add the time that the measure was taken then you can simply use getdate() such as

    SELECT 

    getdate() [Timestamp],
    [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
        ,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
        ,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
        ,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
        ,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
        ,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
        ,[system_memory_state_desc] AS [System_Memory_State_Desc]
    FROM [master].[sys].[dm_os_sys_memory]

    The rest of the plan sounds fine.

    • Proposed as answer by Olaf HelperMVP Thursday, July 2, 2015 10:18 AM
    • Marked as answer by Eric__Zhang Friday, July 10, 2015 1:54 AM
    Thursday, July 2, 2015 10:16 AM
  • Alternative method by creating default constraint 

    CREATE TABLE [dbo].[Tbl1](
    	ID int identity(1,1),
    	[Total_Physical_Memory_In_MB] [BIGINT] ,
    	[Available_Physical_Memory_In_MB] [BIGINT] ,
    	[Total_Page_File_In_MB] [BIGINT] ,
    	[Available_Page_File_MB] [BIGINT] ,
    	[Kernel_Paged_Pool_MB] [BIGINT] ,
    	[Kernel_Nonpaged_Pool_MB] [BIGINT] ,
    	[System_Memory_State_Desc] [NVARCHAR](256),
    	[Crdate] [DATETIME]  DEFAULT(CURRENT_TIMESTAMP)
    ) 
    
    GO
    
    insert into [dbo].[Tbl1]([Total_Physical_Memory_In_MB] ,[Available_Physical_Memory_In_MB],[Total_Page_File_In_MB],
    [Available_Page_File_MB],[Kernel_Paged_Pool_MB],[Kernel_Nonpaged_Pool_MB],[System_Memory_State_Desc] )
    SELECT 
    [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
        ,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
        ,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
        ,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
        ,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
        ,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
        ,[system_memory_state_desc] AS [System_Memory_State_Desc]
    	
    FROM [master].[sys].[dm_os_sys_memory]
    
    select * From [Tbl1]



    Thanks Saravana Kumar C


    • Edited by SaravanaC Thursday, July 2, 2015 10:55 AM
    • Proposed as answer by Eric__Zhang Monday, July 6, 2015 2:33 AM
    • Marked as answer by Eric__Zhang Friday, July 10, 2015 1:54 AM
    Thursday, July 2, 2015 10:54 AM

All replies

  • If you are just looking to add the time that the measure was taken then you can simply use getdate() such as

    SELECT 

    getdate() [Timestamp],
    [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
        ,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
        ,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
        ,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
        ,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
        ,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
        ,[system_memory_state_desc] AS [System_Memory_State_Desc]
    FROM [master].[sys].[dm_os_sys_memory]

    The rest of the plan sounds fine.

    • Proposed as answer by Olaf HelperMVP Thursday, July 2, 2015 10:18 AM
    • Marked as answer by Eric__Zhang Friday, July 10, 2015 1:54 AM
    Thursday, July 2, 2015 10:16 AM
  • Alternative method by creating default constraint 

    CREATE TABLE [dbo].[Tbl1](
    	ID int identity(1,1),
    	[Total_Physical_Memory_In_MB] [BIGINT] ,
    	[Available_Physical_Memory_In_MB] [BIGINT] ,
    	[Total_Page_File_In_MB] [BIGINT] ,
    	[Available_Page_File_MB] [BIGINT] ,
    	[Kernel_Paged_Pool_MB] [BIGINT] ,
    	[Kernel_Nonpaged_Pool_MB] [BIGINT] ,
    	[System_Memory_State_Desc] [NVARCHAR](256),
    	[Crdate] [DATETIME]  DEFAULT(CURRENT_TIMESTAMP)
    ) 
    
    GO
    
    insert into [dbo].[Tbl1]([Total_Physical_Memory_In_MB] ,[Available_Physical_Memory_In_MB],[Total_Page_File_In_MB],
    [Available_Page_File_MB],[Kernel_Paged_Pool_MB],[Kernel_Nonpaged_Pool_MB],[System_Memory_State_Desc] )
    SELECT 
    [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
        ,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
        ,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
        ,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
        ,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
        ,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
        ,[system_memory_state_desc] AS [System_Memory_State_Desc]
    	
    FROM [master].[sys].[dm_os_sys_memory]
    
    select * From [Tbl1]



    Thanks Saravana Kumar C


    • Edited by SaravanaC Thursday, July 2, 2015 10:55 AM
    • Proposed as answer by Eric__Zhang Monday, July 6, 2015 2:33 AM
    • Marked as answer by Eric__Zhang Friday, July 10, 2015 1:54 AM
    Thursday, July 2, 2015 10:54 AM