After insert trigger question - SQL 2008

Question

I have data coming in from datastage that is being put in our SQL Server 2008 database in a table: stg_table_outside_data. The ourside source is putting the data into that table every morning. I want to move the data from stg_table_outside_data to table_outside_data where I keep multiple days worth of data.

I created a stored procedure that inserts the data from stg_table_outside_Data into table_outside_data and then truncates stg_table_outside_Data. The outside datastage process is outside of my control, so I have to do this all within SQL server 2008. I had originally planned on using a simple after insert statement, but datastage is doing a commit after every 100,000 rows. The trigger would run after the first commit and cause a deadlock error to come up for the datastage process.

Is there a way to set up an after insert to wait 30 minutes then make sure there wasn't a new commit within that time frame? Is there a better solution to my problem? The goal is to get the data out of the staging table and into the working table without duplications and then truncate the staging table for the next morning's load.

I appreciate your time and help.

Answer 1

You shouldn't be using a trigger to do this, you should use a scheduled job.

Answer 2

One way you could do this is take advantage of the new MERGE statement in SQL Server 2008 (see the MSDN docs and this blog post) and just schedule that as a SQL job every 30 minutes or so.

The MERGE statement allows you to easily just define operations (INSERT, UPDATE, DELETE, or nothing at all) depending on whether the source data (your staging table) and the target data (your "real" table) match on some criteria, or not.

So in your case, it would be something like:

MERGE table_outside_data AS target
USING stg_table_outside_data AS source 
ON (target.ProductID = source.ProductID)   -- whatever join makes sense for you

WHEN NOT MATCHED THEN
    INSERT VALUES(.......)

WHEN MATCHED THEN 
    -- do nothing

Answer 3

maybe building a procedure that moves all data from stg_table_outside_Data to table_outside_data once a day, or by using job scheduler.