SQL Server Performance

Insert values in single table

Discussion in 'SQL Server 2005 General Developer Questions' started by sasivashok, Jan 26, 2010.

  1. sasivashok New Member

    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
  2. preethi Member

    As this post came under SQL Server 2005 area, I believe you are working with the same.
    If your table UserPermission has a unique key on the above three columns, you can simply add ignore duplicate key and it will allow you to insert everything but the duplicate entries.
    Insert into UserPermission
    select a.UserID, B.*
    from (Select distinct UserID from UserPermission) a
    cross join UserFeaturetype b
    If not you can use the following query.
    Insert into UserPermission
    select a.UserID, B.*
    from ((Select distinct UserID from UserPermission) a
    cross join UserFeaturetype b)
    Left join UserPermission c on a.UserId= c.UserId and b.Usertypeid = c.Usertypeid and b.featureoperationid = c.featureoperationid
    Where c.UserId is null
    You can also use except keyword to do the same thing.
    In SQL 2008, merge statement can be used to do the insert, along with updates and deletes

Share This Page