How to insert custom values with INSERT INTO + SELECT FROM?

Question

i would like to insert custom values along with table columns when i perform INSERT INTO ...SELECT FROM ... WHERE clause

INSERT INTO RoleMappingEmployee_Delete_History (
    RoleMappingEmployeeKey,
    SrKey,
    RoleKey,
    SubmittedDate,
    SubmittedBy,
    IsActive,
    DeletedBy,
    DeletedDateTime)
SELECT 
    RoleMappingEmployeeKey,
    SrKey,
    RoleKey,
    SubmittedDate,
    SubmittedBy,
    IsActive,
    DeletedBy,
    DeletedDateTime
FROM
    RoleMappingEmployee
WHERE
    RoleMappingEmployeeKey IN (25902,38188,25887)

i would like to insert values for DeletedBy,DeletedDateTime with custom values

Why do you want to customize the log records (DeletedBy,...)? — p2k, Sep 29 '16 at 10:12
`DeletedBy,DeletedDateTime`, have null values on insertion or better said on creation of roles but on removal of roles the `delete` log is recorded — coolstoner, Sep 29 '16 at 10:16

score 17 · Accepted Answer · answered Sep 29 '16 at 09:55

INSERT INTO RoleMappingEmployee_Delete_History (
    RoleMappingEmployeeKey,
    SrKey,
    RoleKey,
    SubmittedDate,
    SubmittedBy,
    IsActive,
    DeletedBy,
    DeletedDateTime)
SELECT 
    RoleMappingEmployeeKey,
    SrKey,
    RoleKey,
    SubmittedDate,
    SubmittedBy,
    IsActive,
    'peter',
    getdate()
FROM
    RoleMappingEmployee
WHERE
    RoleMappingEmployeeKey IN (25902,38188,25887)

score 0 · Answer 2 · answered Sep 29 '16 at 10:58

You can also set DeletedBy using ORIGINAL_LOGIN function, it will return the username who is performing the operation and help to avoid setting up value every time or parameter for this. Similarly you can use alternative in DeletedDateTime using CURRENT_TIMESTAMP as below:

INSERT INTO RoleMappingEmployee_Delete_History (
    RoleMappingEmployeeKey,
    SrKey,
    RoleKey,
    SubmittedDate,
    SubmittedBy,
    IsActive,
    DeletedBy,
    DeletedDateTime)
SELECT 
    RoleMappingEmployeeKey,
    SrKey,
    RoleKey,
    SubmittedDate,
    SubmittedBy,
    IsActive,
    ORIGINAL_LOGIN(),
    CURRENT_TIMESTAMP
FROM
    RoleMappingEmployee
WHERE RoleMappingEmployeeKey IN (25902,38188,25887)

How to insert custom values with INSERT INTO + SELECT FROM?

2 Answers2

Linked