Insert values from one table to another table

[Guvera]

Hi,

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

 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

 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

[si_the_geek]

This is one way you could do it:

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

[FunkyDexter]

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

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.