Sqlserver如何把查询的数据insert进入新表

首先建立新表：

然后用SQL实现把查询的数据insert进入新表：
Insert into test.dbo.tmp_tj001(server_principal_name,object_name,maxdate)
select s1.server_principal_name,s1.object_name,max(s1.event_time) as maxdate from
(SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_920000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and  class_type='U'  
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_520000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and  class_type='U' 
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_450000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and  class_type='U' 
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_650000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and  class_type='U' 
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_980000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and  class_type='U' 
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_730000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and  class_type='U' 
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_430000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and  class_type='U' 
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_670000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and  class_type='U' 
union all
SELECT distinct server_principal_name,object_name,event_time
FROM sys.fn_get_audit_file('\\10.0.2.116\Audit\Audit_380000.sqlaudit ', NULL, NULL)
WHERE database_name = 'HR'and action_id='CR' and  class_type='U')s1
group by s1.server_principal_name,s1.object_name

亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>