Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
zoe2003
Starting Member
17 Posts |
Posted - 2013-12-03 : 11:32:30
|
Hi,Ho can I generate the following result using nested loop and variables :col1 col21 51 61 72 52 62 73 53 63 74 54 64 7Tx,Z |
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2013-12-03 : 11:41:22
|
You would not use a loop.select * from (select 1 as col1 union select 2 union select 3 union select 4) across apply(select 5 as col2 union select 6 union select 7) border by a.col1, b.col2 How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
zoe2003
Starting Member
17 Posts |
Posted - 2013-12-03 : 14:33:39
|
Thanks for the reply.Actually I need a loop because it will be a 17M records table. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-12-03 : 14:47:47
|
17M rows is exactly why you shouldn't use a loop. When you're ready to step out of the world of procedural programming and into set-based programming (meaning: when you're ready to vastly improve your sql programming skill) tell us what you're really trying to achieve and we can help.declare @c1 bigintdeclare @c2 bigintcreate table #t (col1 bigint, col2 bigint)set @c1 = 1set @c2 = 5while @c1 < 5begin while @c2 < 8 begin insert #t (col1, col2) values (@c1, @c2) set @c2 = @c2+1 end set @c1 = @c1+1 set @c2 = 5endselect * from #tdrop table #t Be One with the OptimizerTG |
|
|
zoe2003
Starting Member
17 Posts |
Posted - 2013-12-04 : 04:26:04
|
Thanks, it's great ! |
|
|
Kristen
Test
22859 Posts |
Posted - 2013-12-04 : 05:13:05
|
quote: Originally posted by zoe2003 Thanks, it's great !
No it isn't! Its utter rubbish - just like TG told you it would be, it will perform like a dog and, on 17M records, may well bring your server to its knees, or run it out of disk space extending the Log File (which, if you have a DBA, may well land you in deep doo-doo, and if you don't may well crash some other business critical database on that server).This is the key point in TG's message:"tell us what you're really trying to achieve and we can help" |
|
|
|
|
|
|
|