February 3, 2012 at 3:12 am
You are welcome.
The problem with cross join is it ill iterate for (size of tableA)*(size of tableB) times.
But its more a conceptual problem.
The performance of any join (and most where clauses) ill be bosted bt proper indexes.
You can find a lot of articles about performance (cursos, joins, subqueries, indexes) here in SSC.
Take you time for learn.
😉
February 3, 2012 at 6:23 am
Thank you all. Since the output of the result needs to be inserted into another table. The other option would be to do this programmatically with loops , if-then-else statements.
February 3, 2012 at 6:28 am
Why? You still can do it in the "relational way"
create table TableA (textname char(3))
GO
insert into tableA values ('ABC'),('DEF'),('GHI')
GO
create table TableB (id int, ind tinyint, textname char(3))
GO
create table TableC (id int, ind tinyint, textnameB char(3), textnameA char(3))
GO
insert into tableB values
(101,1,'ABC')
,(102,1,'ABC')
,(103,2,'ABC')
,(104,2,'ABC')
,(105,1,'DEF')
,(105,1,'DEF')
,(106,2,'DEF')
,(107,1,'GHI')
,(108,2,'GHI')
GO
insert into TableC
select b.id, b.ind, b.textname, a.textname
from tableA a
cross join TableB b
where (a.textname = b.textname) or (a.textname <> b.textname and b.ind = 1)
select * from tableC
GO
drop table tableA
drop table tableB
drop table tableC
GO
February 5, 2012 at 2:23 pm
svakka (2/3/2012)
Thank you all. Since the output of the result needs to be inserted into another table. The other option would be to do this programmatically with loops , if-then-else statements.
Gosh, doing the same thing with loops will make the cross join seem like lightning.
--Jeff Moden
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply