Insert values from one table to another table

Hi,

I have two tables. Table Name 1 : UserPermission Table Name 2: UserFeaturetype.

Code:

---

UserPermission                                                                        UserFeaturetype

============                                                                        =============

 UserId  Usertypeid  featureoperationid                                        Usertypeid            featureoperationid

60            3                  5                                                            3                              1

60            3                  6                                                            3                              3

60            3                  7                                                              3                              8

                                                                                                  3                              5

                                                                                                  3                              6

                                                                                                  3                              7

---

What is my query that in UserPermission table i need to insert UserFeaturetype table values without already inserted featureoperationid that is 5,6,7 is already inserted in Table 1. I need to avoid duplication that is need to insert 1,3,8.

My output could be like that is

Code:

---

60      3      5

60      3      6

60      3        7

60      3      1

60      3        3

60      3      8

---

Give me suggestion. Hope's your reply soon.



Thanks

This is one way you could do it:

Code:

---

INSERT INTO UserPermission (UserId, Usertypeid, featureoperationid)
  SELECT 60, UF.Usertypeid, UF.featureoperationid
  FROM UserFeaturetype UF
      LEFT JOIN UserPermission UP ON (UF.Usertypeid = UP.Usertypeid AND UF.featureoperationid = UP.featureoperationid)
  WHERE UP.Usertypeid Is Null

---

Si's query is right but another way (and one I personally find a bit more readable) is to use a NOT EXISTS clause:-

Code:

---

INSERT INTO UserPermission (UserId, Usertypeid, featureoperationid)
  SELECT 60, UF.Usertypeid, UF.featureoperationid
  FROM UserFeaturetype UF
  WHERE NOT EXISTS
    (SELECT * FROM UserPermission UP
    WHERE UF.Usertypeid = UP.Usertypeid AND UF.featureoperationid = UP.featureoperationid)

---

The performance should be roughly similar.