TSQL Get max value and use that insert rows into another table that has no identify

Question

I need to insert new records into another table. The original table does NOT have an identity column. I wrote the below query to do the insert, but it's inserting 0 records. I'm trying to avoid using a cursor.

Powerpath.dbo.med_specialty  MAX(id) = 57
Connect_Onprem.dbo.med_specialty MAX(id) = 55

I would expect to see 2 rows inserted into Connect_Onprem.dbo.med_specialty.

I'm incrementing the ID + 1 to not get a primary key violation.

Any help is much appreciated!

INSERT INTO Connect_Onprem.dbo.med_specialty(id, code, name, active)
        SELECT 
        ms.id,
        ms.code,
        ms.name,
        ms.active
        FROM Powerpath_Dev.dbo.med_specialty ms  
        JOIN Connect_Onprem.dbo.med_specialty cms  ON ms.id = cms.id
        WHERE ms.active = 'Y'
        and ms.id = (SELECT MAX(id)+1 from 
               Connect_Onprem.dbo.med_specialty ms1 where ms1.id = ms.id)

Answer 1

Could just insert (or merge) the values where not exists already?

INSERT INTO Connect_Onprem.dbo.med_specialty(id, code, name, active)
SELECT 
    ms.id,
    ms.code,
    ms.name,
    ms.active
FROM
    Powerpath_Dev.dbo.med_specialty SRC  
    LEFT JOIN Connect_Onprem.dbo.med_specialty TGT  ON TGT.id = SRC.id AND SRC.active = 'Y'
WHERE
    TGT.id IS NULL