Query Help. - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help.

Author

Topic

mavershang
Posting Yak Master

111 Posts

Posted - 2010-05-11 : 18:53:20

Hi there, here is my quesiton.

table 1
name_col feat1_col
A 12
B 10
C 2
...

Table 2
name_col feat2_col
A 100
B 201
C 1000
...

Now I want to join the two tables randomly for 1000 times to get large table like
feat1_col feat2_col randomization_tag
12 201 1
10 1000 1
2 100 1
...
12 1000 2
10 100 2
2 201 2
...

Is there any extreme fast way to do this work?

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-11 : 19:13:25

for publishing lots of data you can use recursive cte and cross join like this:

declare @t1 table(i int)
insert @t1 select 1 union select 2

declare @t2 table(j int)
insert @t2 select 100 union select 200

;with rc1 as
(select i,1 as l from @t1 
union all
select i, l+1 from rc1 where l<100)
,rc2 as
(select j,1 as l from @t2 
union all
select j, l+1 from rc2 where l<100)

select i,j,rc1.l from rc1 cross join rc2

mavershang
Posting Yak Master

111 Posts

Posted - 2010-05-11 : 20:02:00

Hi ms65g, thanks a lot for your reply.
The recursive ste is a excellent idea. But after the final cross join, I feel like it is not a randomization. could you please explain that a little bit more?

quote:

Originally posted by ms65g

for publishing lots of data you can use recursive cte and cross join like this:

declare @t1 table(i int)
insert @t1 select 1 union select 2

declare @t2 table(j int)
insert @t2 select 100 union select 200

;with rc1 as
(select i,1 as l from @t1 
union all
select i, l+1 from rc1 where l<100)
,rc2 as
(select j,1 as l from @t2 
union all
select j, l+1 from rc2 where l<100)

select i,j,rc1.l from rc1 cross join rc2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources