CODEWITHSUNDEEP
sqlsql-server
alwaysVBNET
alwaysVBNET

Reputation: 3310

Insert records based on existing entries of another table incrementally

I have two tables. Folders and FolderPermission There are folders that for some reason do not have permissions so I'm trying to assign the permissions to the folders without permissions based on a model folderID=1207

I have the following query which doesn't really work. I'm really close though. One of the problems is that the newly Inserted folderID must not exist in the database table FolderPermission because it won't allow duplicate entries. Therefore, the FolderID must be entered based on the records which don't exist. Here is my code:

INSERT INTO FolderPermission (FolderID, PermissionID,AllowAccess,RoleID,UserID,CreatedByUserID,CreatedOnDate,LastModifiedByUserID,LastModifiedOnDate)
          SELECT FolderID, PermissionID,AllowAccess,RoleID,UserID,CreatedByUserID,CreatedOnDate,LastModifiedByUserID,LastModifiedOnDate
          FROM FolderPermission
          WHERE FolderID=1207
SELECT FolderID
FROM Folders 
WHERE FolderID NOT IN (SELECT DISTINCT FolderID  
                         FROM FolderPermission  )

Upvotes: 0

Views: 56

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can combine the two queries into one by using cross join from the model to the list of ids:

INSERT INTO FolderPermission (FolderID, PermissionID, AllowAccess, RoleID, UserID, 
                              CreatedByUserID, CreatedOnDate, LastModifiedByUserID,
                              LastModifiedOnDate
                             )
    SELECT f.FolderID, PermissionID, AllowAccess, RoleID,UserID, CreatedByUserID, CreatedOnDate,
           LastModifiedByUserID, LastModifiedOnDate
    FROM FolderPermission fp cross join
         (SELECT FolderID
          FROM Folders 
          WHERE FolderID NOT IN (SELECT DISTINCT FolderID  
                                 FROM FolderPermission
                                )
         ) f
    WHERE fp.FolderID = 1207;

Upvotes: 1

Related Questions