CODEWITHSUNDEEP
sqlsql-server
Sachith Wickramaarachchi
Sachith Wickramaarachchi

Reputation: 5862

How to insert records based on another table value

I have the following three tables:

Permission

| PermissionId | PermissionName |
+--------------+----------------+
| 1            | A              |
| 2            | B              |
| 3            | C              |
| 100          | D              |

Group

| GroupId | GroupLevel | GroupName            |
+---------+------------+----------------------+
| 1       | 0          | System Administrator |
| 7       | 0          | Test Group 100       |
| 8       | 20         | Test Group 200       |
| 9       | 20         | test                 |
| 10      | 50         | TestGroup01          |
| 11      | 51         | TestUser02           |
| 12      | 52         | TestUser03           |

GroupPermission

| GroupPermissionId | FkGroupId | FkPermissionId |
+-------------------+-----------+----------------+
| 1                 | 1         | 1              |
| 2                 | 1         | 2              |
| 3                 | 1         | 3              |
| 4                 | 1         | 4              |

I need to insert records into GroupPermission table, if table Group, GroupLevel column have 0 then I need to take its GroupId and need to insert values to GroupPermission table as that particular id and 100.

In order to above sample table records, I need to insert the following two records to GroupPermission table,

| FkGroupId | FkPermissionId |
+-----------+----------------+
| 1         | 100            |
| 7         | 100            |

How can I do it

Upvotes: 0

Views: 76

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

This question is not very clear and I can only assume the value 100 is a static value and that you don't actually have foreign keys as the names of the columns imply. Also, you really should avoid reserved words like "Group" for object names. It makes things more difficult and confusing.

The simple version of your insert might look like this.

insert GroupPermission
(
    FkGroupId
    , FkPermissionId
)
select g.GroupId
    , 100
from [Group] g
where g.GroupLevel = 0

--EDIT--

Since you want to only insert those rows that don't already exist you can use NOT EXISTS like this.

select g.GroupId
    , 100
from [Group] g
where g.GroupLevel = 0
    AND NOT EXISTS
    (
        select *
        from GroupPermission gp
        where gp.FkGroupId = g.GroupId
            and g.FkPermissionId = 100
    )

Or you could use a left join like this.

select g.GroupId
    , 100
from [Group] g
left join GroupPermission gp on gp.FkGroupId = g.GroupId
            and gp.FkPermissionId = 100
where g.GroupLevel = 0
    and gp.FkGroupId is null

Upvotes: 1

Related Questions