Compare duas tabelas e atualize com endDate

Não tenho certeza se entendi sua pergunta da maneira correta, mas se o que entendi foi verdade, a consulta abaixo compara ProfileId de duas tabelas e atualiza o campo endDate com a data atual, ele também insere profileIds da tabela newdata que não existem em CustomerProfile (o campo customerId não está disponível para eles) e preenche o campo StartDate com a data atual:

drop table if exists #groupedProfileIds
drop table if exists #groupedProfileIds_agg
select * INTO #groupedProfileIds 
from(SELECT ProfileId ,1 as flag FROM CustomerProfile
    UNION 
    SELECT ProfileId, 2 as flag FROM NewData)  as #tmp
-- we use this flag to determine the table which record came from-------
select * INTO #groupedProfileIds_agg
from (select  ProfileId,sum(flag) as flag_agg
      from #groupedProfileIds
      group by ProfileId) #temp2
-- we used second temp table to store summation of flag to avoid using aggregate function in where clause  
-- when flag is 1  it shows the ProfileId only exists in CustomerProfile, 
--when it is 2 the ProfileId only exists in NewData and it can be 3 if same profileId is available in both ----------

update DBO.CustomerProfile set endDate = GETDATE()
        where  ProfileId in (select ProfileId from #groupedProfileIds_agg
                                where flag_agg = 1) 
    
insert INTO DBO. CustomerProfile (ProfileId ,startDate) 
select ProfileId,getdate()
from #groupedProfileIds_agg
    where flag_agg=2
    

A melhor maneira de fazer isso é criar uma consulta que tenha os dados desejados. Então você pode decidir o que inserir e o que atualizar.

Se o id do perfil não estiver em CustomerProfile, você deseja inserir uma nova linha com um customerId, não diga de onde você obtém esse customerId, pois tudo o que você tem é um profileId?

A deficiência pela qual você especifica seu requisito é a razão pela qual você não encontra uma solução. E ninguém pode ajudá-lo porque você não diz o que quer. De repente, inserir "câncer" em algum lugar torna tudo muito pior!

Ninguém pode responder sua pergunta porque você não diz o que quer. No entanto, se fizermos algumas suposições, o melhor que fazemos é traçar uma direção que você pode seguir.

Podemos adivinhar alguma coisa do seu esquema de tabela incompleto?

CREATE TABLE CustomerProfile (
  customerProfileId integer PRIMARY KEY,
  customerId        integer NULL,
  profileId         integer,
  startDate         datetime,
  endDate           datetime NULL
)
  
CREATE TABLE NewData (
  profileId         integer
)

A forma como está escrito deve haver uma tabela Customer e uma tabela Profile

CREATE TABLE Customer (
  customerId        integer PRIMARY KEY,
  ...
)
  
CREATE TABLE Profie (
  profileId         integer PRIMARY KEY,
  ...
)

então suas tabelas que você mencionou devem ser preenchidas como:

CREATE TABLE CustomerProfile (
  customerProfileId integer PRIMARY KEY,
  customerId        integer REFERENCES Customer(customerId),
  profileId         integer REFERENCES Profile(profileId),
  startDate         timestamp,
  endDate           timestamp
)
  
CREATE TABLE NewData (
  profileId         integer REFERENCES Profile(profileId)
)

Eu primeiro retive sua não-restrição customerId NULL para me dar uma saída para não ter que me preocupar em como inferir o customerId de um mero profileId ao inserir. Mas então eu imaginei que poderia completar seu esquema, para tê-lo lógico. Isso cria tarefas adicionais para inserir uma nova linha de cliente e perfil, eu sei como posso fazer isso, mas você não pediu, então não vou fazer isso.

Então, o acima é apenas para eu entender o que você quer. Você ainda não disse o suficiente.

Presumo de sua tabela que seu relacionamento entre Cliente e Perfil é de muitos para muitos. E eu duvido disso. Acho que você realmente não pensou em sua lógica de dados de negócios. Mas para o que estou propondo não importa.

Também não estou interessado em coisas do SQL Server, mas usarei o SQL padrão (meu banco de dados é o PostgreSQL). Se você não pensa em SQL (a linguagem, não o seu software), você não será um bom designer/programador/administrador de banco de dados, como quer que você se chame.

No SQL padrão, "datetime" é chamado de "timestamp" e a maneira padrão de obter o timestamp atual é current_timestamp.

Sobre sua data final, você diz:

Se o id não estiver na tabela newData, a tabela CustomerProfile será atualizada com uma data de término.

Então, ao inserir em sua tabela, presumo que startDate = current_timestamp e endDate = NULL.

Finalmente, eu assumo que esses ids são "sequências", mas vou me abster de qualquer extensão de sequência para qualquer banco de dados e apenas determinar o próximo valor de

SELECT max(fooId) + 1 FROM Foo

onde "Foo" pode ser Cliente, Perfil ou Perfil do Cliente.

Com todas essas suposições feitas, começo, como disse, do jeito que você deve sempre começar. Escreva um SELECT que lhe dará a tabela final desejada, ou, aqui, começo com a parte INSERT:

INSERT INTO CustomerProfile(customerProfileId, customerId, profileId, startDate, endDate)
WITH DistinctNewProfileIds AS (
  SELECT DISTINCT profileId FROM NewTable
), MaxCustomerProfileId AS (
  SELECT max(customerProfileId) AS maxCustomerProfileId
    FROM CustomerProfile
), MaxCustomerId AS (
  SELECT max(customerId) AS maxCustomerId
    FROM Customer
), ProfileIdWithOrdinal AS (
  SELECT rank() OVER (ORDER BY profileId) AS ordinal,
         profileId
    FROM DistinctNewProfileIds
)
SELECT maxCustomerProfileId + ordinal AS customerProfileId,
       maxCustomerId + ordinal AS customerId,
       profileId,
       current_timestamp AS startDate,
       NULL AS endDate
  FROM ProfileIdWithOrdinal 
  CROSS JOIN MaxCustomerProfileId
  CROSS JOIN CustomerId

Agora a ATUALIZAÇÃO. Você parece esperar que todos os profileIds que ainda não terminaram apareçam em seu NewData todas as vezes. Inicialmente, entendi que você estenderia o endDate toda vez que encontrar o profileId no NewData. Isso me faz pensar o que acontece se um profileId reaparecer de repente no NewData depois que você já o perdeu uma vez e já adicionou um endDate? Você deveria apagar o endDate com NULL novamente? Você não pediu por isso, então eu não vou fazer isso. Mas você não pensou em seu design se não considerar isso.

Aqui está a partição da sua tabela que você atualiza, após a atualização:

WITH DistinctNewProfileIds AS (
  SELECT DISTINCT profileId FROM NewTable
)
SELECT customerProfileId, customerId, profileId, 
       startDate, 
       CASE WHEN p.profileId IS NULL
            THEN current_timestamp
            ELSE NULL END as endDate
  FROM CustomerProfile cp
  LEFT OUTER JOIN DistinctNewProfileIds p
    ON(p.profileId = cp.profileId)

Então aqui eu realmente redefiniria seu endDate para NULL se o profileId reaparecer. Mas a atualização de qualquer maneira fica muito simples:

UPDATE CustomerProfile 
   SET endDate = current_timestamp
 WHERE profileId NOT IN (SELECT profileId FROM NewTable)

Você executa a parte UPDATE primeiro, depois o INSERT. E você está feito.

A melhor descrição do seu requisito é uma consulta SELECT que fará o que você deseja. O que você INSERT vs. UPDATE é apenas uma questão secundária. Isso é ainda mais verdadeiro se você também tiver que inserir linhas Cliente e Perfil para atender às restrições de chave estrangeira. No PostgreSQL eu faço essas coisas em uma única consulta. Em bancos de dados menores, como Oracle ou MS SQL Server, acho que você precisa executar várias etapas em uma transação.