| Author |
Topic |
|
mayoorsubbu
Yak Posting Veteran
95 Posts |
 Posted - 2014-01-21 : 06:43:50
|
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)
17689 Posts |
Posted - 2014-01-21 : 07:43:14
|
[code]
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)
[/code]
KH
[spoiler]Time is always against us[/spoiler]
|
 |
|
|
mayoorsubbu
Yak Posting Veteran
95 Posts |
Posted - 2014-01-21 : 08:17:39
|
Thank you khtan for the help.
|
|
|
|
mayoorsubbu
Yak Posting Veteran
95 Posts |
Posted - 2014-01-22 : 04:49:02
|
| khtan, I m sorry but this does not produce the desired result. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-01-22 : 06:34:01
|
[code]
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)[/code]
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-22 : 08:42:26
|
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
|
|
|
|
mayoorsubbu
Yak Posting Veteran
95 Posts |
Posted - 2014-01-23 : 09:07:54
|
| Once again thank u visakh and khtan. Will try and revert in case of any issue. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-23 : 13:19:51
|
cool
let us know how you got on!
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
|
Help on T-SQL