adding tables with different sizes

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

General SQL Server Forums

New to SQL Server Programming

adding tables with different sizes

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2011-06-20 : 15:02:46

HI
I am adding columns from couple of tables with different sizes. I dont want to join the table just add columns and make a new table. since tables have different sizes when adding them increases the size of the new table dramatically. for instance in my [dev].[geo_master] table I only have 183 row and in the [bts].[cf07ur00].[geo_dest] table I have 163 row and in [bts].[cf07ur00].[mode] I have 19 rows. when I add them together the size of new table is 566751.

is the any solution to it, just adding column (considering the size of largest table 183) beside each other and put null for the rest which is empty.

SELECT a.[geo_orig] OG
,b.[geo_dest] DG
,c.[mode] MA

FROM [dev].[geo_master] a
,[bts].[cf07ur00].[geo_dest] b
,[bts].[cf07ur00].[mode] c

Previous Topic Topic Next Topic

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-06-20 : 15:35:19

you are creating a Cartesian product (183*163*19)=566751. Use the new(er) ANSI JOIN syntax and include table join correlations.

Be One with the Optimizer
TG

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-20 : 15:39:44

This seems kindof odd... however, I can tell you why you get 566751 records... you're cross joining those three tables, so you get 1 record for every combination of rows from those tables.

ie. 183 X 163 X 19

Maybe this will suit:


SELECT [geo_orig] OG, null DG, null MA From [dev].[geo_master]
Union All
SELECT null OG, [geo_dest] DG, null MA From [bts].[cf07ur00].[geo_dest]
Union All
SELECT null OG, null DG, [mode] MA From [bts].[cf07ur00].[mode]

Or maybe your tables are really this:


SELECT [geo_orig] OG, null DG, null MA From [dev].[geo_master]
Union All
SELECT null OG, [geo_dest] DG, null MA From [bts].[cf07ur00]
Union All
SELECT null OG, null DG, [mode] MA From [bts].[cf07ur00]

Or maybe...


Select
	r = coalesce(A.r,B.r,C.r),
	OG,
	DG,
	MA
From
	(
	Select 
		geo_orig OG,
		r = Row_Number() Over(Order By geo_orig)
	From [dev].[geo_master]
	) A
Full Join
	(
	Select 
		geo_dest DG,
		r = Row_Number() Over(Order By geo_dest)
	From [bts].[cf07ur00].[geo_dest]
	) B
On A.r = B.r
Full Join
	(
	Select 
		mode MA,
		r = Row_Number() Over(Order By mode)
	From [bts].[cf07ur00].[mode]
	) C
On isnull(A.r,B.r) = C.r

Corey

I Has Returned!!

goligol
Posting Yak Master

128 Posts

Posted - 2011-06-20 : 17:09:15

HI COREY,

THE LAST CODES WORK! BUT THE PROBLEM IS NOW I WANT TO ADD ALSO COLUMNS IN EACH TABLE CAN I DO SOMTHIN LIKE:

Select
r = coalesce(A.r,B.r,C.r),
OG,
DG,
MA
From
(
Select
geo_orig OG
,OGIDX = CASE
WHEN LEFT(GEO_ORIG,2)='01' THEN '10000000'
WHEN LEFT(GEO_ORIG,2)='02' THEN '2000000'+RIGHT(GEO_ORIG,1)
WHEN LEFT(GEO_ORIG,2)='03' THEN '3000000'+RIGHT(GEO_ORIG,1)
WHEN LEFT(GEO_ORIG,2)='04' THEN '4'+RIGHT(GEO_ORIG,2)+'00000'
ELSE '5'+SUBSTRING(GEO_ORIG,8,7)
END

r = Row_Number() Over(Order By geo_orig)
From [dev].[geo_master]
) A
Full Join
(
Select
geo_dest DG,
r = Row_Number() Over(Order By geo_dest)
From [bts].[cf07ur00].[geo_dest]
) B
On A.r = B.r
Full Join
(
Select
mode MA,
r = Row_Number() Over(Order By mode)
From [bts].[cf07ur00].[mode]
) C
On isnull(A.r,B.r) = C.r

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-21 : 05:59:22

Yeah, that should work. Just need to add the column in the select list

quote:
Originally posted by goligol

HI COREY,

THE LAST CODES WORK! BUT THE PROBLEM IS NOW I WANT TO ADD ALSO COLUMNS IN EACH TABLE CAN I DO SOMTHIN LIKE:

Select
r = coalesce(A.r,B.r,C.r),
OG,
OGIDX,
DG,
MA
From
(
Select
geo_orig OG
,OGIDX = CASE
WHEN LEFT(GEO_ORIG,2)='01' THEN '10000000'
WHEN LEFT(GEO_ORIG,2)='02' THEN '2000000'+RIGHT(GEO_ORIG,1)
WHEN LEFT(GEO_ORIG,2)='03' THEN '3000000'+RIGHT(GEO_ORIG,1)
WHEN LEFT(GEO_ORIG,2)='04' THEN '4'+RIGHT(GEO_ORIG,2)+'00000'
ELSE '5'+SUBSTRING(GEO_ORIG,8,7)
END

r = Row_Number() Over(Order By geo_orig)
From [dev].[geo_master]
) A
Full Join
(
Select
geo_dest DG,
r = Row_Number() Over(Order By geo_dest)
From [bts].[cf07ur00].[geo_dest]
) B
On A.r = B.r
Full Join
(
Select
mode MA,
r = Row_Number() Over(Order By mode)
From [bts].[cf07ur00].[mode]
) C
On isnull(A.r,B.r) = C.r

Corey

I Has Returned!!

goligol
Posting Yak Master

128 Posts

Posted - 2011-06-22 : 15:23:37

Thank you. It is working:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources