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.

Is the ID in the insert statement automatically generated? How is your two tables designed? — John Odom, May 18 '15 at 14:14

score 6 · Accepted Answer · answered May 18 '15 at 14:22

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
                     );

score 1 · Answer 2 · answered May 18 '15 at 14:13

1

Do an INSERT with SELECT, use NOT EXISTS to make sure no existing company is inserted again. Something like:

insert into license (ID,CompanyID,LicenseStart,LicenseEnd,CreatedDate,AddUser,UpdateUser,UpdateDate,TemplateId)
select ID, theCompanyID, GetDate(), DATEADD(day,14,GETDATE()), null,null,null,null null
from company
where not exists (select 1 from company
                  where company.CompanyID = license.CompanyID)

answered May 18 '15 at 14:13

jarlh

42,561
8
45
63

Yes, I know, Jazz has to do some work here... That's why I wrote "Something like"... – jarlh May 18 '15 at 14:21

score 1 · Answer 3 · answered May 18 '15 at 14:18

1

INSERT INTO License (CompanyID, LicenseStart, LicenseEnd)
SELECT ID, GetDate(), DATEADD(day,14,GETDATE())
FROM Company
WHERE ID NOT IN
(
    SELECT DISTINCT CompanyID
    FROM License
)

answered May 18 '15 at 14:18

Gabriel Rainha

1,713
1
21
34

score 1 · Answer 4 · answered May 18 '15 at 14:30

You can do it with LEFT JOIN:

INSERT  INTO License
        ( CompanyID ,
          LicenseStart ,
          LicenseEnd ,
          CreatedDate ,
          AddUser ,
          UpdateUser ,
          UpdateDate ,
          TemplateId
        )
        SELECT  ID ,
                GETDATE() ,
                DATEADD(DAY, 14, GETDATE()) ,
                NULL ,
                NULL ,
                NULL ,
                NULL ,
                NULL
        FROM    Company c
                LEFT JOIN License l ON l.CompanyID = c.ID
        WHERE   l.ID IS NULL

score 1 · Answer 5 · answered May 18 '15 at 14:30

An alternative way to do is to use the EXCEPT operator which is faster than subquery or join owing to it being a set function.

Insert License

select a.CompanyID, GetDate(), DATEADD(day,14,GETDATE()), null,null,null,null null
from 
    (select id from company 
    except
    select CompanyID from License
    )a

score 1 · Answer 6 · edited May 23 '17 at 12:24

Since OP has said that the ID in the license table is incremented by 1 then you can write your query like this:

INSERT INTO License(CompanyID,LicenseStart,LicenseEnd)
SELECT c.ID, GetDate(), DATEADD(day,14,GETDATE()))
FROM Company c
WHERE NOT EXISTS
(
    SELECT l.CompanyID
    FROM License l
    WHERE l.CompanyID = c.ID
)

You do not have to specify the column in the INSERT statement if the column is automatically incremented. Also I chose to use NOT EXISTS instead of NOT IN in the `WHERE clause for the reason that Martin Smith specified in this Stack Overflow question.

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

6 Answers6