Help on T-SQL - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Help on T-SQL

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources