How to update Audit table in SQL after records has been inserted?

[MissMary]

Good day,

How do i insert this SQL query  alteryx to help me update my File_ Audit table in SQL Server after record has been moved there. It works more like a control/audit table. I have used both post and pre SQL create statement but it won't work. Please i need help

Declare @load_date_time datetime,
@Inserted_Record_Count int;

set @load_date_time = getdate();
set @Inserted_Record_Count = (select Count(*) from [dbo].[school]);

Begin
Update [dbo].[File_Audit]
set load_date_time = @load_date_time,
Inserted_Record_Count = @Inserted_Record_Count
Where Table_Name = 'School';
end;

[HenrietteH · Accepted solution]

Hi @MissMary 

I was playing with the statement and getting an error too, it seems that the variables aren't getting created. 

The best way I found to work around that was by creating a stored procedure and then just calling the stored procedure from the post-sql statement. That worked for me: 

Create  procedure sp_fileaudit as 

Declare @load_date_time datetime,
@Inserted_Record_Count int;

set @load_date_time = getdate();
set @Inserted_Record_Count = (select Count(*) from [dbo].[CO_STORE_FILE]);

Begin
Update [dbo].[File_Audit]
set load_date_time = @load_date_time,
Inserted_Record_Count = @Inserted_Record_Count
Where Table_Name = 'CO_STORE_FILE';
end;

Then call the procedure: 

You can set it up to make the table name a parameter that can be updated to keep that part dynamic. 

[MissMary]

Thank you. What if i want to count deleted record too because some record get deleted on the source and we have to delete on our target too.

[HenrietteH]

Hi @MissMary 

If the records still exist in the target, you can compare source to target and then delete everything that doesn't exist in the source anymore. You would need a way of comparing the records for that, such as a primary key. 

Another option would be to drop all data in the target and replace it with what is in the source. That only works if you are processing all records, which might not be feasible.