代码之家 › 专栏 › 技术社区 › PJD

带Insert语句的CTE

tsql sql

-1

PJD · 技术社区 · 6 年前

我以前得到过这段代码的帮助,但现在我想将结果集添加到一个单独的表变量中 @检查 工会声明?

 DECLARE @customer_dist TABLE
    (
        [id] [INT] NOT NULL ,
        [First_Name] [VARCHAR](50) NULL ,
        [Last_Name] [VARCHAR](50) NULL ,
        [DoB] [DATE] NULL ,
        [post_code] [VARCHAR](50) NULL ,
        [mobile] [VARCHAR](50) NULL ,
        [Email] [VARCHAR](100) NULL
    );

INSERT INTO @customer_dist ( id ,
                             First_Name ,
                             Last_Name ,
                             DoB ,
                             post_code ,
                             mobile ,
                             Email )

select id, first_name, last_name, dob, post_code, mobile, email from checky where NID = 148575

 DECLARE @check TABLE
    (
        [id] [INT] NOT NULL ,
        [NID] [INT] NOT NULL 
    )



;with PairedRows as ( 
  select CDL.id as NId, CDR.id as Id
    from @customer_dist as CDL inner join
      @customer_dist as CDR on CDR.Id > CDL.Id and 
        CDR.First_Name = CDL.First_Name and CDR.Last_Name = CDL.Last_Name and CDR.DoB = CDL.DoB and 
        ( CDR.post_code = CDL.post_code or CDR.mobile = CDL.mobile or CDR.Email = CDL.Email ) 
    where not exists (
      select 42 from @customer_dist as NE where NE.ID < CDL.Id and 
        NE.First_Name = CDL.First_Name and NE.Last_Name = CDL.Last_Name and NE.DoB = CDL.DoB and
        ( NE.post_code = CDL.post_code or NE.mobile = CDL.mobile or NE.Email = CDL.Email ) ) )
  select NId, Id 
    from PairedRows
  union all
  select Min( NId ) as NID, Min( NId ) as Id 
    from PairedRows
    group by NId
  union all
  select id, id 
    from @customer_dist as CD
    where not exists ( select 42 from PairedRows as PR where PR.NId = CD.id or PR.Id = CD.id )
  order by NID, Id

2 回复 | 直到 6 年前

MatBailie 6 年前

WITH
  bah AS
(
  stuff
)
INSERT INTO
  table
SELECT foo FROM bah
UNION ALL
SELECT foo FROM bah
UNION ALL
SELECT foo FROM bah

PJD 6 年前

我已经想好了在哪里加,谢谢你的指点

;with PairedRows as ( 

  select CDL.id as NId, CDR.id as Id
    from @customer_dist as CDL inner join
      @customer_dist as CDR on CDR.Id > CDL.Id and 
        CDR.First_Name = CDL.First_Name and CDR.Last_Name = CDL.Last_Name and CDR.DoB = CDL.DoB and 
        ( CDR.post_code = CDL.post_code or CDR.mobile = CDL.mobile or CDR.Email = CDL.Email ) 
    where not exists (
      select 42 from @customer_dist as NE where NE.ID < CDL.Id and 
        NE.First_Name = CDL.First_Name and NE.Last_Name = CDL.Last_Name and NE.DoB = CDL.DoB and
        ( NE.post_code = CDL.post_code or NE.mobile = CDL.mobile or NE.Email = CDL.Email ) ) )
insert into @check
  select NId, Id 
    from PairedRows
union all
  select Min( NId ) as NID, Min( NId ) as Id 
    from PairedRows
    group by NId
union all
  select id, id 
    from @customer_dist as CD
    where not exists ( select 42 from PairedRows as PR where PR.NId = CD.id or PR.Id = CD.id )
order by NID, Id