IrishCharm
05-27-2011, 07:15 AM
Hi,
I want to basically get 3 tables, say the ones I have below, and get each random instance of all of them in one table. I am trying to do combination tables but this is huge and taking forever - I was wondering if someone has an easier way to do it? An example of the final table what it should look like is also pasted below
Thanks
S
create table #tmpName
(
Name nvarchar(350),
NameID int
)
insert into #tmpName select 'Sarah','1'
insert into #tmpName select 'Marc','2'
insert into #tmpName select 'James','3'
insert into #tmpName select 'Sean','4'
insert into #tmpName select 'Frank','5'
create table #tmpCountry
(
Country nvarchar(350),
CountryID int
)
insert into #tmpCountry select 'Irl','1'
insert into #tmpCountry select 'Uk','2'
insert into #tmpCountry select 'Us','3'
insert into #tmpCountry select 'Aus','4'
create table #tmpProduct
(
Product nvarchar(350),
ProductID int
)
insert into #tmpProduct select 'Hat','1'
insert into #tmpProduct select 'Cloth','2'
insert into #tmpProduct select 'Rose','3'
insert into #tmpProduct select 'Satin','4'
insert into #tmpProduct select 'Shed','5'
insert into #tmpProduct select 'Dog','6'
insert into #tmpProduct select 'Shoe','7'
select * from #tmpName
select * from #tmpCountry
select * from #tmpProduct
'Name:','NameID:','Country:','CountryID:','Product:','ProductID:'
'Sarah','1','Irl','1','Hat','1'
'Sarah','1','Uk','2','Hat','1'
'Sarah','1','Uk','2','Rose','3'
:think:
I want to basically get 3 tables, say the ones I have below, and get each random instance of all of them in one table. I am trying to do combination tables but this is huge and taking forever - I was wondering if someone has an easier way to do it? An example of the final table what it should look like is also pasted below
Thanks
S
create table #tmpName
(
Name nvarchar(350),
NameID int
)
insert into #tmpName select 'Sarah','1'
insert into #tmpName select 'Marc','2'
insert into #tmpName select 'James','3'
insert into #tmpName select 'Sean','4'
insert into #tmpName select 'Frank','5'
create table #tmpCountry
(
Country nvarchar(350),
CountryID int
)
insert into #tmpCountry select 'Irl','1'
insert into #tmpCountry select 'Uk','2'
insert into #tmpCountry select 'Us','3'
insert into #tmpCountry select 'Aus','4'
create table #tmpProduct
(
Product nvarchar(350),
ProductID int
)
insert into #tmpProduct select 'Hat','1'
insert into #tmpProduct select 'Cloth','2'
insert into #tmpProduct select 'Rose','3'
insert into #tmpProduct select 'Satin','4'
insert into #tmpProduct select 'Shed','5'
insert into #tmpProduct select 'Dog','6'
insert into #tmpProduct select 'Shoe','7'
select * from #tmpName
select * from #tmpCountry
select * from #tmpProduct
'Name:','NameID:','Country:','CountryID:','Product:','ProductID:'
'Sarah','1','Irl','1','Hat','1'
'Sarah','1','Uk','2','Hat','1'
'Sarah','1','Uk','2','Rose','3'
:think: