Insert records based on a condition

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 based on a condition

Author

Topic

donjt81
Starting Member

11 Posts

Posted - 2012-05-25 : 13:48:49

My table looks like this right now

CustomerID TechID
1__________1
1__________2
1__________3
2__________5
3__________2
3__________3
4__________2
5__________2

This is what I am trying to do. I have introduced a new TechType (in a different table) with TechID = 6. For every customer that has TechID of 2, I want to also add TechID 6. so in the above scenario my table would look like this.

CustomerID TechID
1__________1
1__________2
1__________3
1__________6
2__________5
3__________2
3__________3
3__________6
4__________2
4__________6
5__________2
5__________6

I am often faced with this type of insert and I somehow make it work with #temptables etc. but there has to be an elegant way of doing this. Can anyone help please.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-05-25 : 13:56:49

Don't knwo about elegant, but...
DECLARE @table Table (CustomerID int,TechID int)
INSERT INTO @table
VALUES
(1,1),
(1,2),
(1,3),
(2,5),
(3,2),
(3,3),
(4,2),
(5,2)
select * from @table

INSERT INTO @table
SELECT customerID,6
FROM @table t1
WHERE exists(select * from @table t2 where t1.CustomerID = t2.CustomerID and t1.TechID =2)

select * from @table order by 1,2

Jim

Everyday I learn something that somebody else already knew

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-25 : 13:58:19

INSERT myTable(CustomerID, TechID)
SELECT CustomerID, 6 AS TechID
FROM myTable A
WHERE TechID=2
AND NOT EXISTS(SELECT * FROM myTable WHERE CustomerID=A.CustomerID AND TechID=6)