SQL procedure for inserting records in a table based on records in another table

I have a FormTable

| FormId | FormName | ParentLawId |  StartDate | Frequency |
|--------|----------|-------------|------------|-----------|
|      1 |    FormA |          21 | 2017-11-06 |   Monthly |
|      2 |    FormB |          22 | 2016-12-06 |    Yearly |
|      3 |    FormC |          24 | 2015-06-08 |  Quaterly |
|      4 |    FormD |          24 | 2018-02-11 |   Monthly |

I have another CalendarTable

| CalendarId | LawId |  StartDate | Frequency | FormId |
|------------|-------|------------|-----------|--------|
|          1 |    21 | 2017-11-06 |   Monthly |      1 |
|          2 |    24 | 2015-06-08 |  Quaterly |      3 |

I want to insert records in CalendarTable for the forms(in FormTable) which do not have their respective records in the CalendarTable.

For example, the final CalendarTable should be

| CalendarId | LawId |  StartDate | Frequency | FormId |
|------------|-------|------------|-----------|--------|
|          1 |    21 | 2017-11-06 |   Monthly |      1 |
|          2 |    24 | 2015-06-08 |  Quaterly |      3 |
|          3 |    22 | 2016-12-06 |    Yearly |      2 |
|          4 |    24 | 2018-02-11 |   Monthly |      4 |

So, 2 new records have been generated in CalendarTable corresponding to FormId(FormTable) column, and rest of the column values in CalendarTable should be as it is in corresponding columns from FormTable

SQL Fiddle for Schema of the two Tables

http://sqlfiddle.com/#!9/e337d0/1

Answer

Use this query inside your stored procedure

Note:keeping calender id as autoincrement

insert into CalendarTable(LawId, StartDate, Frequency, FormId)
select ParentLawId,StartDate,Frequency,FormId
from FormTable  where formid not in(select formid from CalendarTable);

Advertisement

Answer