Menu
I have this situation:
drop table #t1;
drop table #t2
select *
into #t1
from
(select 'va1'c1,'vb1'c2,'vc1'c3 union all
select 'va2'c1,'vb2'c2,'vc2'c3 union all
select 'va3'c1,'vb3'c2,'vc3'c3 union all
select 'va1'c1,'vb1'c2,'vc1'c3 union all
select 'va2'c1,'vb2'c2,'vc2'c3 union all
select 'va3'c1,'vb3'c2,'vc3'c3 union all
select 'va1'c1,'vb1'c2,'vc1'c3 union all
select 'va2'c1,'vb2'c2,'vc2'c3 union all
select 'va3'c1,'vb3'c2,'vc3'c3 union all
select 'va1'c1,'vb1'c2,'vc1'c3 union all
select 'va2'c1,'vb2'c2,'vc2'c3 union all
select 'va3'c1,'vb3'c2,'vc3'c3 union all
select 'va4'c1,'vb4'c2,'vc4'c3) t
select *
into #t2
from #t1
where 0 = 1
;with tmp1 as
(
select
t1.*,
ROW_NUMBER() over (partition by t1.c1 order by (select null)) r
from
#t1 t1
left join
#t2 t2 on t1.c1 = t2.c1
where
t2.c1 is null
), tmp2 as
(
select
0 n,*
from
tmp1
union all
select
n+1 n, t1.c1, t1.c2, t1.c3, t1.r
from
tmp2 t1
join
tmp1 t2 on t1.c1 = t2.c1
and t2.r = t1.r + 1
where
n < 10
)
--insert #t2
select c1, c2, c3 --,r
from tmp2
When I run this, it selects everything just fine (103 records).
The problem is when I this code to insert into #t2 (13 records!!!)
I think SQL runs step by step and insert records during running and than my condition in tmp1 is over...
How to resolve it?
My goal is to check if data exists, than loop and insert results...but SQL stops after 1st cycle...
940
You can also use CTE to insert data into the SQL table. The CTE query definition includes the required data that you can fetch from existing tables using joins.
It only persists for a single statement, and then automatically disappears. Redefine the CTE a second time. This is as simple as copy-paste from WITH... through the end of the definition to before your SET . Put your results into a #temp table or a @table variable.
A CTE is similar to a derived table in that it is not stored and lasts only for the duration of the query. Unlike a derived table, a CTE behaves more like an in-line view and can be referenced multiple times in the same query. Using a CTE makes complex queries easier to read and maintain.
Not only can you define multiple CTEs and reference them in a single SELECT statement, but you can also have a CTE that references another CTE. In order to do this all you need to do is define the referenced CTE prior to using it. Here is an example where my first CTE is referenced inside the second CTE definition.
You could use MERGE:
select * into #t1
from(
select 'va1'c1,'vb1'c2,'vc1'c3 union all
select 'va2'c1,'vb2'c2,'vc2'c3 union all
select 'va3'c1,'vb3'c2,'vc3'c3 union all
select 'va1'c1,'vb1'c2,'vc1'c3 union all
select 'va2'c1,'vb2'c2,'vc2'c3 union all
select 'va3'c1,'vb3'c2,'vc3'c3 union all
select 'va1'c1,'vb1'c2,'vc1'c3 union all
select 'va2'c1,'vb2'c2,'vc2'c3 union all
select 'va3'c1,'vb3'c2,'vc3'c3 union all
select 'va1'c1,'vb1'c2,'vc1'c3 union all
select 'va2'c1,'vb2'c2,'vc2'c3 union all
select 'va3'c1,'vb3'c2,'vc3'c3 union all
select 'va4'c1,'vb4'c2,'vc4'c3
)t;
select * into #t2 from #t1 where 0=1;
;with tmp1 as(
select t1.*, ROW_NUMBER()over(partition by t1.c1 order by(select null))r
from #t1 t1
left join #t2 t2
on t1.c1=t2.c1
where t2.c1 is null
),tmp2 as (
select 0 n,*
from tmp1
union all
select n+1 n,t1.c1,t1.c2,t1.c3,t1.r
from tmp2 t1
join tmp1 t2
on t1.c1=t2.c1
and t2.r=t1.r+1
where n<10
)
MERGE #t2
USING tmp2
ON #t2.c1 = tmp2.c1
WHEN NOT MATCHED THEN
INSERT VALUES (tmp2.c1, tmp2.c2, tmp2.c3);
SELECT @@ROWCOUNT;
-- 103
DBFiddle Demo
EDIT:
Kudos to Bartosz Ratajczyk for examining this case:
It turns out it is related to lazy/eager table/index spooling. There are at least two more ways to force SQL Server to generate different execution plan:
a) By using TOP (100) PERCENT
DECLARE @n INT = 100;
;with tmp1 as (
select t1.*,
ROW_NUMBER()over(partition by t1.c1 order by(select null))r
from #t1 t1
left join #t2 t2
on t1.c1=t2.c1
where t2.c1 is null
),tmp2 as
(
select 0 n,*
from tmp1
union all
select n+1 n,t1.c1,t1.c2,t1.c3,t1.r
from tmp2 t1
join tmp1 t2
on t1.c1=t2.c1
and t2.r=t1.r+1
where n<10
)
insert #t2
select TOP (@n) PERCENT c1, c2, c3 --,r
from tmp2
SELECT @@ROWCOUNT;
b) By using ORDER BY .. OFFSET 0 ROWS:
;with tmp1 as(
select t1.*,
ROW_NUMBER()over(partition by t1.c1 order by(select null))r
from #t1 t1
left join #t2 t2
on t1.c1=t2.c1
where t2.c1 is null
),tmp2 as
(
select 0 n,*
from tmp1
union all
select n+1 n,t1.c1,t1.c2,t1.c3,t1.r
from tmp2 t1
join tmp1 t2
on t1.c1=t2.c1
and t2.r=t1.r+1
where n<10
)
insert #t2
select c1, c2, c3 --,r
from tmp2
ORDER BY 1 OFFSET 0 ROWS;
SELECT @@ROWCOUNT;
db<>fiddle demo2
149
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
