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

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;

Help

Accepted answers

hhaigh1

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.

All comments

hhaigh1

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703