Insert records from Same table

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

Insert records from Same table

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-07-14 : 09:37:42

I have a table that holds Customer Part numbers.


Cus_no   item_no
 100      ABC
 100      XYZ
 200      ABC
 300      XYZ

Looking at the data above. I want look at customer 100 and insert all of customer 100's records where other customers do not match.

Table name ItemCust

After running my script I would expect my data to look like:


Cus_no   item_no
 100      ABC
 100      XYZ
 200      ABC
 200      XYZ
 300      ABC
 300      XYZ

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-14 : 10:11:34

Hi, There are so many different ways to do this, but here is one

CREATE TABLE #C
(
CUS_NO int,
Item_no varchar(5)

)

INSERT INTO #C
VALUES(100 , 'ABC'),
(100 , 'XYZ'),
(200 , 'ABC'),
(300 , 'XYZ')

INSERT INTO #C(CUS_NO,Item_no)
SELECT DISTINCT CUS_NO,I.Item_no FROM #C C
CROSS JOIN (SELECT DISTINCT Item_no FROM #C) I
WHERE NOT EXISTS
( SELECT 1 FROM #C Ci
WHERE Ci.CUS_NO = C.CUS_NO
AND Ci.Item_no = I.Item_no
)

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-07-14 : 11:10:25

What if I just want to copy and insert the records from customer 100 specifically?

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-14 : 11:42:20

You could swap to something like this, If I am understanding what you mean.
INSERT INTO #C(CUS_NO,Item_no)
SELECT i.CUS_NO,C.Item_no
FROM
(SELECT CUS_NO,Item_no FROM #C WHERE CUS_NO = 100) C
CROSS APPLY (SELECT DISTINCT CUS_NO FROM #C WHERE CUS_NO != 100) I
WHERE NOT EXISTS
( SELECT 1 FROM #C Ci
WHERE Ci.CUS_NO = I.CUS_NO
AND Ci.Item_no = C.Item_no
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources