if object_id('pubs..tb') is not null
drop table tb
go
create table tb
(
tid int,
aa int,
bb int,
cc int,
dd int,
ee int
)
insert into tb(tid,aa,bb,cc,dd,ee) values(1, 1, 3, 7, 8, 9)
insert into tb(tid,aa,bb,cc,dd,ee) values(2, 4, 6, 7, 8, 10 )
insert into tb(tid,aa,bb,cc,dd,ee) values(3, 3, 5, 7, 8, 9)
create table test
(
a int,
b int,
c int,
d int,
e int
)
declare @a as int
declare @b as int
declare @c as int
declare @d as int
declare @e as int
declare @aa as int
declare @bb as int
declare @cc as int
declare @dd as int
declare @ee as int
set @a = 1
set @b = 1
set @c = 1
set @d = 1
set @e = 1
/*WHILE @a <= 10
begin
set @b = 1
while @b <= 10
begin
set @c = 1
while @c <= 10
begin
while @d <= 10
begin
while @e <= 10
begin
insert into test(a,b,c,d,e) values(@a,@b,@c,@d,@e)
set @e = @e + 1
end
set @d = @d + 1
end
set @c = @c + 1
end
set @b = @b + 1
end
set @a = @a + 1
end
*/
WHILE @a <= 10
begin
set @b = 1
while @b <= 10
begin
set @c = 1
while @c <= 10
begin
set @d = 1
while @d <= 10
begin
set @e = 1
while @e <= 10
begin
insert into test(a,b,c,d,e) values(@a,@b,@c,@d,@e)
set @e = @e + 1
end
set @d = @d + 1
end
set @c = @c + 1
end
set @b = @b + 1
end
set @a = @a + 1
end
select * from test where cast(a as varchar) + cast(b as varchar) + cast(c as varchar) + cast(d as varchar) + cast(e as varchar) not in
(select cast(aa as varchar) + cast(bb as varchar) + cast(cc as varchar) + cast(dd as varchar) + cast(ee as varchar) from tb)
drop table tb,test