My Table is
emp_activity
id emp_id date time status ---|------|-------------|----------|--------- 1 | 1122 | 20-08-2019 | 09:30 AM | login 2 | 1123 | 20-08-2019 | 09:30 AM | login 5 | 1124 | 20-08-2019 | 09:30 AM | login 3 | 1125 | 20-08-2019 | 09:30 AM | login 4 | 1126 | 20-08-2019 | 09:30 AM | login 6 | 1127 | 20-08-2019 | 09:30 AM | login 7 | 1128 | 20-08-2019 | 09:30 AM | login 8 | 1129 | 20-08-2019 | 09:30 AM | login
i want to insert each employees logout rows at 11 PM on this same day in same table like this.
Wanted Table emp_activity
id emp_id date time status ---|------|-------------|----------|--------- 1 | 1122 | 20-08-2019 | 09:30 AM | login 2 | 1123 | 20-08-2019 | 09:30 AM | login 5 | 1124 | 20-08-2019 | 09:30 AM | login 3 | 1125 | 20-08-2019 | 09:30 AM | login 4 | 1126 | 20-08-2019 | 09:30 AM | login 6 | 1127 | 20-08-2019 | 09:30 AM | login 7 | 1128 | 20-08-2019 | 09:30 AM | login 8 | 1129 | 20-08-2019 | 09:30 AM | login 9 | 1122 | 20-08-2019 | 11:00 PM | logout 10 | 1123 | 20-08-2019 | 11:00 AM | logout 11 | 1124 | 20-08-2019 | 11:00 AM | logout 12 | 1125 | 20-08-2019 | 11:00 AM | logout 13 | 1126 | 20-08-2019 | 11:00 AM | logout 14 | 1127 | 20-08-2019 | 11:00 AM | logout 15 | 1128 | 20-08-2019 | 11:00 AM | logout 16 | 1129 | 20-08-2019 | 11:00 AM | logout
Advertisement
Answer
Just use union all with scalar value insead of a column:
select * from MyTable
union all
select id, emp_id, date, cast('11:00:00' as time), 'logout' from MyTable