CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012sql-server-2014
JohnG
JohnG

Reputation: 272

TSQL - clone records for a day

I'm working on creating a Lab with a big MSSQL database. To simplify the problem, Let's assume that it's for 1 table only (If I can find a better solution, I can do it for all tables)

I have data for 1 day in a table, say between 525k and 630k records. I want to duplicate the data for different days (create history).

I've already tried different ways and I'm finding it very long. initially the process was taking 91 hours to duplicate 1 day... I brought it down to 16 minutes to process 1 day (which makes approx. 91 hours to duplicate a year) . I'm wondering if there is any tool or something made to duplicate data or create a history quickly?

Here's what I have right now:

    Declare @iDateCnt int=1,
            @TmpDate datetime,
            @iDate int=365, -- counter to create a years worth of history.
            @DateStart datetime = '2015-12-22'
    Select F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, getdate() as F11
    into #TMP_Table1
    From Table1 where F1 = @DateStart -- Template Day to be duplicated

While @iDateCnt<=@iDate
Begin 
    Set @TmpDate = @DateStart-@iDateCnt
    Delete from Table1 where F1 = @TmpDate
    Insert into Table1 (F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11)
        Select @TmpDate as F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, getdate() as F11
        from #Tmp_Table1

    Drop #Tmp_Table1
End

Upvotes: 0

Views: 56

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31785

To formulate my comment into an answer, you would start by creating a table that has a datetime column, with one row for every date that you want to populate with cloned data. Say we call it tblDates. Then you would just do this to your existing script:

Select F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, getdate() as F11
into #TMP_Table1
From Table1 where F1 = @DateStart -- Template Day to be duplicated

    Insert into Table1 (F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11)
        Select tblDates.DtColumn as F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, getdate() as F11
        from #Tmp_Table1
        CROSSJOIN tblDates

    Drop #Tmp_Table1
End

This will create a copy of all the data in #TMP_Table1 for each row in tblDates, with the date from tblDates in the F1 column (where you were previously using the loop variable).

Upvotes: 1

Jamiec
Jamiec

Reputation: 136154

Doing one insert at a time will always be slow, especially with a heavily indexed table.

Instead you should do this as a single insert, something along the lines of

DECLARE @startDate DATETIME = '2015-12-22'
DECLARE @endDate = DATEADD(days,365, @startDate)

Insert into Table1 (F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11)        
Select F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, getdate() 
FROM Table1 where F1 > @startDate AND F1 <= @endDate

Upvotes: 1

Related Questions