而不是CTE使用表变量 [英] Instead of CTE using a Table Variable

问题描述

如何将CTE更改为Table变量，因为我需要将其用于Insert和Delete。

声明@rc int; 

declare @rc int; 

sami

推荐答案

如何将CTE更改为Table变量，因为我需要将它用于Insert和Delete。

How can I change from CTE to Table variable as I need to use it for both Insert and Delete.

你的代码是将一个表的内容移动到另一个表中？你确定你的代码有效吗？

The purpose of your code is to move the contents of one table to another in blocks? Are you sure your code works?

如果您的代码有效，请尝试：

If your code works, try:

-- code #1
-- create #tb table structure
if object_id ('tempdb..#tb', 'U') is not null 
  drop table #tb;
go
select id, id1, descr
  into #tb
  from test1
  where 1 = 0;
--create unique clustered index I1_TB on #tb (id);
go

declare @rc int; 
declare @tableRows int;
declare @batchSize int;
declare @start int;
declare @end int;

set @rc=1;
select @tablerows=count(*) from test1 t;
set @batchsize=2;
set @start=1;
set @end = @start + @batchSize - 1;

--
while @rc < @tableRows
  begin
  truncate table #tb;
  with cte as
  (
  select *, ROW_NUMBER() over(order by id) as 'RowNbr' 
    from test1 (NOLOCK)
  )
  insert into #tb (id, id1, descr)
    select id, id1, descr 
      from cte
      where RowNbr between @start and @end;

  insert into test2 with (tablock) (id, id1, Descr)
    select id,id1,descr 
      from #tb;
    
  select count(*) from test2;

  DELETE test1 
    WHERE EXISTS (SELECT * FROM #tb as tb WHERE tb.id=TEST1.id  AND  RowNbr <= @end);
  SELECT @END;
  
  set @rc += @batchSize;
  set @start = @end + 1;
  set @end = @start + @batchSize - 1;
  end;

drop table #tb;

我没有测试;
可能包含错误。

I didn't test; may contain error(s).

这篇关于而不是CTE使用表变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！