Hi Guys,

Could you please help me with writing a T_SQL query for the following scenario

I have a SQL DB Audit file that gets populated with data as the activity on DB goes on.

I have multiple monthly tables setup that the import should go into these monthly tables based on the event_time value in the SQL DB Audit file.

for Ex: all the data Like event_time '2015-08-25 15:59:39.033' should go to SQL table Audit_tbl_Aug2015

--Query for reading SQLDB Audit file 

SELECT * FROM sys.fn_get_audit_file ('C:\backup\Audit\*',default,default)
order by event_time desc
GO
--DML for Audit table 
CREATE TABLE [dbo].[Audit_tbl_Aug2015](
 [id] [bigint] IDENTITY(1,1) NOT NULL,
 [event_time] [datetime2](7) NOT NULL,
 [sequence_number] [int] NULL,
 [action_id] [varchar](4) NULL,
 [succeeded] [bit] NOT NULL,
 [permission_bitmask] [bigint] NOT NULL,
 [is_column_permission] [bit] NOT NULL,
 [session_id] [smallint] NOT NULL,
 [server_principal_id] [int] NULL,
 [database_principal_id] [int] NULL,
 [target_server_principal_id] [int] NULL,
 [target_database_principal_id] [int] NULL,
 [object_id] [bigint] NULL,
 [class_type] [varchar](10) NULL,
 [session_server_principal_name] [nvarchar](100) NULL,
 [server_principal_name] [nvarchar](100) NULL,
 [server_principal_sid] [nvarchar](100) NULL,
 [database_principal_name] [nvarchar](100) NULL,
 [target_server_principal_name] [nvarchar](100) NULL,
 [target_server_principal_sid] [nvarchar](100) NULL,
 [target_database_principal_name] [nvarchar](100) NULL,
 [server_instance_name] [nvarchar](100) NULL,
 [database_name] [nvarchar](100) NULL,
 [schema_name] [nvarchar](100) NULL,
 [object_name] [nvarchar](100) NULL,
 [statement] [nvarchar](max) NULL,
 [additional_information] [nvarchar](500) NULL,
 [file_name] [nvarchar](500) NULL,
 [audit_file_offset] [bigint] NULL,
 [user_defined_event_id] [int] NULL,
 [user_defined_information] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
 [event_time] ASC,
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

Please let me know if more information is required

Any help is greatly appreciated

thanks

Bhanu

 

Sounds like you need a partitioned table