SQL - Copy row multiple times changing date field each time-CodePudding

I need to be able to copy a row of data multiple times and update a date field with each copy. So if the row that needs to be copied has a date of say 2022-12-01, I need that to be copied until the date field contains 2022-10-15 (can be any start/end dates depending upon what needs to be copied). This is something that is currently being done in Excel and then copy/pasted into a table but I would prefer to do it in SQL if possible.

Thanks in advance for any help you can provide.

I have not tried anything because I honestly don't know where to start.

Update - I was just given some code by a co-worker that is used to copy a row of data and insert with a different date. Can this be modified to be some sort of loop to go through a range of dates and employee ids?


Insert INTO tblUsers2
                        (ExtractDate, Username, EmployeeID, LastName, FirstName, MiddleInitial, Suffix, StartDate, EndDate, IsSupervisor, IsTeamLead, Department, Dept_Desc, 
                          UserLocation, CP_NCP, Account, AccountGroup, AccountOrganization, Supervisor, Level8, Level7, Level6, Level5, SVPName, Level3, Level2, Level1, JobTitle, 
                          GenesysLogon, Email, CreateDate, EmployeeDeptID, CeridianDate, Work_Phone, Home_Phone, Mobile_Phone, SeniorityDate, Location, TZSTDName, TZDisplayName,
                          Last_Hire_Date, Original_Hire_Date, Benefit_Calc_Date, MiddleName, GaxEmployeeID, WFO, samaccountname, Assigned_Role)
    select     '2023-1-18 00:00:00.000' as extractdate, Username, EmployeeID, LastName, FirstName, MiddleInitial, Suffix, StartDate, EndDate, IsSupervisor, IsTeamLead, Department, Dept_Desc, 
                          UserLocation, CP_NCP, Account, AccountGroup, AccountOrganization, Supervisor, Level8, Level7, Level6, Level5, SVPName, Level3, Level2, Level1, JobTitle, 
                          GenesysLogon, Email, CreateDate, EmployeeDeptID, CeridianDate, Work_Phone, Home_Phone, Mobile_Phone, SeniorityDate, Location, TZSTDName, TZDisplayName,
                          Last_Hire_Date, Original_Hire_Date, Benefit_Calc_Date, MiddleName, GaxEmployeeID, WFO, samaccountname, Assigned_Role
    FROM         CapacityPlanning.dbo.tblusers2
    where ExtractDate = '1/19/2023' and employeeID in ('1019464','1019499','1019520','1016615','1019454','1019482');

CodePudding user response：

Try this: CROSS JOIN with an in-line table consisting of consecutive integers, starting from 0:

CREATE TABLE startrow (dt DATE, other_col VARCHAR(20));
INSERT INTO startrow
SELECT
 '2022-12-01'
,'other column data'
;

WITH
i(i) AS (
      SELECT 0
UNION SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9
)
SELECT
  DATEADD(dd,i,dt) AS dt
, other_col
FROM startrow CROSS JOIN i
ORDER BY 1;
-- out      dt     |     other_col     
-- out ------------ -------------------
-- out  2022-12-01 | other column data
-- out  2022-12-02 | other column data
-- out  2022-12-03 | other column data
-- out  2022-12-04 | other column data
-- out  2022-12-05 | other column data
-- out  2022-12-06 | other column data
-- out  2022-12-07 | other column data
-- out  2022-12-08 | other column data
-- out  2022-12-09 | other column data
-- out  2022-12-10 | other column data

CodePudding user response：

Thanks to everyone's input, I was able to come up with the following:

``
DECLARE @StartDate AS DATE = '2022-10-01 00:00:00.000'
DECLARE @EndDate AS DATE = '2022-09-07 00:00:00.000'

WHILE @StartDate >= @EndDate

BEGIN

Insert INTO tblUsers2
                    (ExtractDate, Username, EmployeeID, LastName, FirstName, MiddleInitial, Suffix, StartDate, EndDate, IsSupervisor, IsTeamLead, Department, Dept_Desc, 
                      UserLocation, CP_NCP, Account, AccountGroup, AccountOrganization, Supervisor, Level8, Level7, Level6, Level5, SVPName, Level3, Level2, Level1, JobTitle, 
                      GenesysLogon, Email, CreateDate, EmployeeDeptID, CeridianDate, Work_Phone, Home_Phone, Mobile_Phone, SeniorityDate, Location, TZSTDName, TZDisplayName,
                      Last_Hire_Date, Original_Hire_Date, Benefit_Calc_Date, MiddleName, GaxEmployeeID, WFO, samaccountname, Assigned_Role)
SELECT  @StartDate AS extractdate, Username, EmployeeID, LastName, FirstName, MiddleInitial, Suffix, StartDate, EndDate, IsSupervisor, IsTeamLead, Department, Dept_Desc, 
                      UserLocation, CP_NCP, Account, AccountGroup, AccountOrganization, Supervisor, Level8, Level7, Level6, Level5, SVPName, Level3, Level2, Level1, JobTitle, 
                      GenesysLogon, Email, CreateDate, EmployeeDeptID, CeridianDate, Work_Phone, Home_Phone, Mobile_Phone, SeniorityDate, Location, TZSTDName, TZDisplayName,
                      Last_Hire_Date, Original_Hire_Date, Benefit_Calc_Date, MiddleName, GaxEmployeeID, WFO, samaccountname, Assigned_Role
FROM         CapacityPlanning.dbo.tblusers2
WHERE ExtractDate = '9/06/2022' and employeeID in ('118093','1021528')

SET @StartDate = CAST(@StartDate AS DATETIME) - 1;

END;