SQL Trigger insert

Question

Hi Guys,

What is the best way to set up a trigger to get all new rows in a table and insert them into a work queue table.

Many Thanks Sp

Answer 1

With this trigger:

CREATE TRIGGER InsertIntoWorkingTable
    ON VeryOldHugeDatabaseTable
    INSTEAD OF INSERT 
AS
    insert into WorkingTable (column1, column2, column3...) (
        select column1, column2, column3
            from inserted
    )

All you have to do is to keep inserting into your VeryOldHugeDatabaseTable. The trigger will get busy inserting into this working table.

Once a while, you could simply launch a stored procedure through a job or something alike to insert or archive into this old table from the working table.

Answer 2

A search for "t-sql trigger" returns this as the first result:

Create Trigger

Other than the syntax, you pretty much answered your own question. Set up a trigger AFTER INSERT and put an insert statement in it.

Answer 3

To answer the question you asked

CREATE TRIGGER dbo.tr_i_BaseTable 
   ON  dbo.BaseTable 
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

   INSERT INTO WorkTable
   SELECT * FROM INSERTED

END

However instead of actually duplicating the rows twice can you not use some other attribute of the rows to detect "New" ones that should be considered part of the worktable. Such as ID or adding an "inserted" date/time field.

Or another approach might be to just start afresh with the new WorkTable and consider all previous records to be archived. You can create a (possibly Partitioned) View UNION ALL-ing the 2 tables for those queries that need to work on the consolidated set.