仅将新数据从一个表插入具有不同结构的新数据

Question

仅将新数据从一个表插入另一个表，并具有不同的结构

大家好，我正在尝试在" userhr.nucleofam_ris"表中插入" userhr.paghe_nucleo_fam"中的额外数据。但是出现错误：

"子查询返回了1个值。

问题是我正在尝试为每个发现的记录输入更高的 1 kint。

SELECT distinct
       (select ROW_NUMBER() OVER (ORDER BY kint ASC) + 11000 from userhr.nucleofam_ris) as kint,
       hr01.kint as [kpadre]
      ,hr01.kint as [risorsa]
      ,hr05.parentela
      ,null as [gradoparentela]
      ,hr05.codfisc as [codfisc]
      ,hr05.cognome as [cognome]
      ,hr05.nome as [nome]
      ,concat(hr05.cognome,' ',hr05.nome) as [nominativo]
      ,hr05.sesso as [sesso] 
      ,hr05.datanasc as [nasc_data]
      ,null as [comune]
      ,null as [codice]
      ,null as [provincia]
      ,null as [stato]
      ,'NO' [disabile]
      ,null as [percdisab]
      ,'NO' AS [parto_plurimo]
      ,null as  [data_decesso]
      ,null as [data_presnascita_figlio]
      ,'NO' AS [acarico]
      ,'NO' AS [flessibilita]
      ,'NO' AS [sost_coniuge]
  FROM userhr.ris_umane hr01 INNER JOIN 
       userhr.wwrapp_ris hr03 ON (hr01.kint = hr03.kpar) INNER JOIN 
       userhr.rapp_lavoro_ris hr04 ON (hr03.karr = hr04.kint) INNER JOIN 
       userhr.paghe_nucleo_fam hr05 ON (hr04.kint = hr05.kpadre) 
where hr05.codfisc is not null 
      and hr05.cognome is not null 
      and hr05.nome is not null
      and NOT EXISTS (SELECT 1 
                      FROM userhr.nucleofam_ris hr02 
                      WHERE hr02.codfisc = hr05.codfisc)

你能帮我吗？

Answer 1

我认为您要创建下一组键，以便可以在现有表中的最后一行之后插入您的行。

这是一个使用两个临时表变量来执行此操作的示例。它应该指向正确的方向。

declare @t1 table(k int, txt1 varchar(10))
insert @t1 values (1,'firstrow'),(2,'second'),(3,'third')
declare @t2 table(txt2 varchar(10))
insert @t2 values ('a'),('b'),('c')
select row_number() over(order by txt2)
    +(select max(k) from @t1) NewKey, 
    txt2 
from @t2 t2

结果是：

NewKey  txt2
4       a
5       b
6       c