Automatically inserted rows in SQL Server 2014

Question

I am quite new in SQL Server, so i'll ask my question directly:

Let's say I have two tables

• A (a1 type1, ..., ai DATE, frequence INT, ...,aN typeN)
  • frequence represents the number of days
• B (b1 type1,..., bi typei, ...., bN typeN)

What I want to do is to put a kind of listener on the table A that listens the ''ai and frequence'' attributes of each row, and then inserts rows into the table B, example :

1. (v1, ...., "2016-03-01",30, ....,vN) a row of table A, so a new row is added to the table B every month beginning from the date "2016-03-01"

So a new row is inserted in the table B on : "2016-04-01", "2016-05-01", "2016-06-01" and so on.

2. the new inserted B row can be calculated according on information contained in Table A (insert is just an example of a process)

I've already looked for this question, and the most relevant answer that I found is to use triggers, but triggers are fired after or instead of an INSERT/UPDATE/DELETE and those operations are hard coded, it's not the DBMS (or the current date in the example above) that initiate the process, it's the client.

Every remark is welcome.

Thank you, and see you soon below.

Answer 1

It looks like you need a scheduled job because you don't want the rows to be inserted into table B at the time they are inserted into A. The scheduled job can call a stored procedure or execute the SQL query directly.

Here's the syntax for creating a stored procedure:

CREATE PROCEDURE BuildBFromA
AS 
BEGIN
    DECLARE @Numbers TABLE
    (
         Number int Primary Key
    )

    --You get the idea
    INSERT into @Numbers
    VALUES (0),(1),(2),(3)

    --Put your insert query here.
    insert B (b1, bi, bN)
    Select a1, a1, aN
    from a
    join @Numbers n on 1=1
    where DATEADD(a.aN,n.Number,a.ai) = convert(date,getdate())
END
GO

Here's the syntax for adding a scheduled job:

USE msdb ;
GO
EXEC dbo.sp_add_job
    @job_name = N'Build B' ;
GO
EXEC sp_add_jobstep
    @job_name = N'Build B',
    @step_name = N'Insert Rows in B based on A',
    @subsystem = N'TSQL',
    @command = N'EXECUTE BuildBFromA', 
    @retry_attempts = 5,
    @retry_interval = 5 ;
GO
EXEC dbo.sp_add_schedule
    @schedule_name = N'RunEveryMinute',
    @freq_type = 4,
    @active_start_time = 233000;
USE msdb ;
GO
EXEC sp_attach_schedule
   @job_name = N'Build B',
   @schedule_name = N'RunEveryMinute';
GO
EXEC dbo.sp_add_jobserver
    @job_name = N'Build B';
GO

Normally I would create an example on SQLFiddle.com, but the site doesn't seem to be working now.