How To Use Loop In Stored Procedure To Copy Data From One Table To Another In Sql Server

I am working on SQL Server. I have a table Trans_Details with these columns: empId transDateTime Created_Date LeaveStatus I have another table Trans_Leave: EMPLOYEE_NUMBER

Solution 1:

You can use insert ... select to copy rows without a loop. I've added the statements for an "upsert" as requested in your comment.

insert  Trans_Details
        (empId, transDateTime, Created_date, LeaveStatus)
select  employee_number
,       date_start
,       getdate()
,       name
from    Trans_Leave tl
where   start_date between'2015-06-01'and'2015-06-10'andnotexists
        (
        select*from    Trans_Details td
        where   transDateTime between'2015-06-01'and'2015-06-10'and tl.employee_number = td.empId
        )

update  td
set     transDateTime = date_start
,       Created_date = now()
,       LeaveStatus = name
from    Trans_Details td
join    Trans_Leave tl
on      tl.employee_number = td.empId
where   transDateTime between'2015-06-01'and'2015-06-10'and date_start between'2015-06-01'and'2015-06-10'

Solution 2:

To be able to easily populate a range of dates, you should create a table that contains all the possible days you'll ever need (e.g. 1.1.2010 - 31.12.2099), one date per row. With that table this is easy to solve.

Assuming you create table with name DATES and column CALENDARDATE is the date, you can just join it with your data, something like this:

insertinto XXX
select
  L.EMPLOYEE_NUMBER,
  L.NAME,
  D.CALENDARDATE
from
  DATES D
  Trans_Leave L   
where
  D.CALENDARDATE >= L.DATE_START and
  D.CALENDARDATE <= L.DATE_END