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.
| Author |
Topic |
|
Chap
Starting Member
30 Posts |
 Posted - 2010-02-25 : 13:28:03
|
Createing a trigger to insert into table2 from table1
Each table has two columns, UserId and CustNum
Both are primary keys with UserId a foreign key to another table. The combination of the two columns must be unique.
Ex:
Table1: Table2
UserId CustNum UserId CustNum
5 1 5 1
5 2 5 2
5 3 5 3
6 22 6 22
Trigger works if new UserId is entered in table1 Ex:
Table1: Table2
UserId CustNum UserId CustNum
5 1 5 1
5 2 5 2
5 3 5 3
6 22 6 22
7 15 7 15
But if a new CustNum is entered in table1 with the same UserId
Table1: Table2
UserId CustNum UserId CustNum
5 1 5 1
5 2 5 2
5 3 5 3
6 22 6 22
7 15 7 15
7 17
Table2 does not update. I am using the following query:
Insert into [Table2(Userid,CustNum)
SELECT Userid,CustNum
FROM Table1
WHERE UserId not in (select UserId from Table1 )
Tried adding
and CustNum not id(select custnum from Table1)
with no success. Where am I going wrong?
George |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-02-25 : 14:03:47
|
in a trigger you can make use of a trigger table called inserted at runtime.
So your statement for new rows should be:
insert table2(userid, CustNum)
select userid, custnum from inserted
Read about triggers in books online...
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-25 : 14:22:11
|
Duplicate: Please answer here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140457
Chap: If you post duplicate posts in multiple forums different people start answering each post and then find they have wasted their time and probably feel less inclined to help you in the future. |
|
|
|
|
|
|
trigger to insert into table2 from table1