Insert Exclusive Data from 2 Tables

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)

Insert Exclusive Data from 2 Tables

Author

Topic

rekiller
Starting Member

31 Posts

Posted - 2009-08-17 : 17:20:52

Hi there. I have a problem. Cant get exclusive data from a simple relation. I have this:


DECLARE @CATALOG TABLE (RangeNumber Integer, RangeDesc Varchar(50))
INSERT INTO @CATALOG 
select 1,'1. 0 Days' UNION
select 2,'2. To 30 Days' UNION
select 3,'3. To 60 Days' UNION
select 4,'4. To 90 Days' UNION
select 5,'5. To 120 Days' UNION
select 6,'6. To 180 Days' UNION
select 7,'7. To 360 Days' UNION
select 8,'8. + 360 Days' UNION
select 9,'9. Due'


DECLARE @DATA TABLE (MonthNumber Integer, RangeNumber Integer, Balance Decimal(18,2))

INSERT INTO @DATA
SELECT 1,1,1330.22 UNION
SELECT 1,1,1108137210.22 UNION
SELECT 1,2,60761625.83 UNION
SELECT 1,3,22261917.52 UNION
SELECT 1,4,8706707.32 UNION
SELECT 1,8,15397510.09 UNION
SELECT 2,1,3453 UNION
SELECT 2,2,345 UNION
SELECT 2,3,43534 UNION
SELECT 2,6,345334 UNION
SELECT 2,7,35345

I want to add rows in Data table from ranges that i dont have in data table but i have in the Catalog.

I want to Add the following list:


SELECT 1,5,0 UNION
SELECT 1,6,0 UNION
SELECT 1,7,0 UNION
SELECT 1,8,0 UNION
SELECT 2,4,0 UNION
SELECT 2,5,0 UNION
SELECT 2,8,0 UNION
SELECT 2,9,0

Notice that in month number 1, i didnt have 5,6,7,8 and in month number 2 i didnt have 4,5,8,9 ranges, so i want to added with 0 Balance.

How can i do these?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-17 : 17:31:20

try this:


select m.monthNumber
       ,c.rangeNumber
       ,0 as balance
from  
       (select monthNumber from @data group by monthNumber) m
cross join @catalog c
left outer join @data d
       on d.monthNumber = m.monthNumber
       and d.RangeNumber = c.rangeNumber
where  d.monthNumber is null

Be One with the Optimizer
TG

rekiller
Starting Member

31 Posts

Posted - 2009-08-17 : 17:42:14

Thks,
That's correct!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources