loops and cursor

Question

Post reply

loops and cursor

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

jcb SSCertifiable Points: 6574 More actions · Answer 1

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.

😉

svakka SSC Eights! Points: 908 More actions · Answer 2

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.

jcb SSCertifiable Points: 6574 More actions · Answer 3

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')

,(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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)