SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Help on T-SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mayoorsubbu
Yak Posting Veteran

India
94 Posts

Posted - 01/21/2014 :  06:43:50  Show Profile  Reply with Quote
Dear All,
I have a table tbItem in SQL Server 2000 as below.
Datatype int
PrimaryItem SecondaryItem
1111 98766
1111 65438
1111 45324
2222 51332
2222 87656

I want to insert data into the same table with different combinations of primaryItem and SecondaryItem like under

PrimaryItem SecondaryItem
98766 1111
98766 65438
98766 45324
65438 1111
65438 98766
65438 45324
45324 1111
45324 98766
45324 65438
51332 2222
51332 87656
87656 2222
87656 51332

Can I do it use set based operations or is a cursor based approach required. How do I achieve it?

Thank You

khtan
In (Som, Ni, Yak)

Singapore
17432 Posts

Posted - 01/21/2014 :  07:43:14  Show Profile  Reply with Quote

insert into tbItem ( PrimaryItem , SecondaryItem )
select PrimaryItem , SecondaryItem 
from
(
    select distinct PrimaryItem from tbItem 
) p
cross join
(
    select distinct SecondaryItem from tbItem 
) s
where not exists (select * from tbItem x where x.PrimaryItem = p.PrimaryItem and x.SecondaryItem = s.SecondaryItem)



KH
Time is always against us

Go to Top of Page

mayoorsubbu
Yak Posting Veteran

India
94 Posts

Posted - 01/21/2014 :  08:17:39  Show Profile  Reply with Quote
Thank you khtan for the help.
Go to Top of Page

mayoorsubbu
Yak Posting Veteran

India
94 Posts

Posted - 01/22/2014 :  04:49:02  Show Profile  Reply with Quote
khtan, I m sorry but this does not produce the desired result.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17432 Posts

Posted - 01/22/2014 :  06:34:01  Show Profile  Reply with Quote

insert into tbItem ( PrimaryItem , SecondaryItem )
select PrimaryItem , SecondaryItem 
from
(
    select distinct PrimaryItem from tbItem 
    union
    select distinct SecondaryItem from tbItem 
) p
cross join
(
    select distinct SecondaryItem from tbItem 
    union
    select distinct PrimaryItem from tbItem 
) s
where not exists (select * from tbItem x where x.PrimaryItem = p.PrimaryItem and x.SecondaryItem = s.SecondaryItem)



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/22/2014 :  08:42:26  Show Profile  Reply with Quote
quote:
Originally posted by mayoorsubbu

Dear All,
I have a table tbItem in SQL Server 2000 as below.
Datatype int
PrimaryItem SecondaryItem
1111 98766
1111 65438
1111 45324
2222 51332
2222 87656

I want to insert data into the same table with different combinations of primaryItem and SecondaryItem like under

PrimaryItem SecondaryItem
98766 1111
98766 65438
98766 45324
65438 1111
65438 98766
65438 45324
45324 1111
45324 98766
45324 65438
51332 2222
51332 87656
87656 2222
87656 51332

Can I do it use set based operations or is a cursor based approach required. How do I achieve it?

Thank You



INSERT INTO TableName
(PrimaryItem,SecondaryItem)
SELECT SecondaryItem,
PrimaryItem
FROM TableName
UNION ALL
SELECT t1.SecondaryItem,t2.SecondaryItem
FROM TableName t1
INNER JOIN TableName t2
ON t2.PrimaryItem = t1.PrimaryItem
WHERE t1.SecondaryItem <> t2.SecondaryItem


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mayoorsubbu
Yak Posting Veteran

India
94 Posts

Posted - 01/23/2014 :  09:07:54  Show Profile  Reply with Quote
Once again thank u visakh and khtan. Will try and revert in case of any issue.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/23/2014 :  13:19:51  Show Profile  Reply with Quote
cool
let us know how you got on!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000