SQL - Insert new row into an existing table based on another table entry

Question

I have a company table and a licence table. I need to insert a new row in the license table for every company in the company table that isn't already in the license table.

License (ID,CompanyID,LicenseStart,LicenseEnd,CreatedDate,AddUser,UpdateUser,UpdateDate,TemplateId) The ID in this table is incremented by 1 when a new row is added.

Company (ID,Name,CreateDate,CState,LocationID,LegalName)

The default value that would be entered for each CompanyID that isn't already in the license table should look something like this.

Insert (ID, @theCompanyID, GetDate(), DATEADD(day,14,GETDATE()), null,null,null,null null)

@theCompanyID would be the CompanyID that isn't in the license table

I am very new to this so any help would be appreciated.

Answer 1

license.id is an identity column, so you do not need to insert it.

insert into license (CompanyID, LicenseStart, LicenseEnd)
    select c.id, GetDate(), DATEADD(day, 14, GETDATE())
    from company c
    where not exists (select 1
                      from license l
                      where c.ID = l.CompanyID
                     );

You also don't need to insert explicit NULL values for columns where you are not supplying values. The default is to set these values to NULL.

If your start and end dates do not have a time component -- just the date -- then use this instead:

    select c.id, cast(GetDate() as date), cast(DATEADD(day, 14, GETDATE()) as date)
    from company c
    where not exists (select 1
                      from license l
                      where c.ID = l.CompanyID
                     );