0

I have a problem where I need to copy data from one database into another, I am never guaranteed to have the data available in realtime, and sometimes I might need to process 5 days' of data in one go etc. The processing day should operate over a period from 7am one day to 6:59am the next day, for example, between 2017-08-01 07:00 and 2017-08-02 06:59.

I would like to use SQL but without any looping constructs is possible, but I am do not know how to convert the psuedo code below into the required SQL statements, thus any help would be appreciated:

declare @lastDate datetime
set @lastDate = (select max(dc) from dmt)

do
    if gettime() => 07:00 then
        if @lastDate < getdate() then
            insert id, dc into dmt where dc between @lastdate and @lastdate + 1 day from othertable
        end if
    end if

    set @lastDate = (select max(dc) from dmt)
while @lastDate < getdate()

I am using SQL Express 2012. The id is an int and dc is a datetime. dmt is the table where I want to store the copied information and othertable is where I want to copy from.

Thanks.

beliskna
  • 185
  • 11
  • 1
    Look into 'Cursor' examples – Sparrow Aug 09 '17 at 19:50
  • Is the source and destination different for each day? Is there a reason you can't just do it all at once as opposed to looping for each day? – Xedni Aug 09 '17 at 20:03
  • I'm not sure what you mean Xedni, new records are added to the other table everyday for all different times of the day, and each day I need to summarise them and copy them into the dmt table because they are dropped from other table every week. Only the summarised data will be retained and I need to ensure that the dmt contains a summarised record for each day. – beliskna Aug 09 '17 at 20:19

1 Answers1

0

this is how the code would look like, by using SQL, and i think you need a sql job task to be scheduled in the time of the day you need, please check this about creating jobs with sql server Express Edition

How to create jobs in SQL Server Express edition

    declare @lastDate datetime
set @lastDate = (select max(dc) from dmt)


INSERT INTO DMT(ID,DC)

    SELECT ID,DC 
    FROM OTHER_TABLE
    WHERE DC BETWEEN @LASTDATE AND DATEADD(DAY,1,@lastDate)
    AND DATEPART(HOUR,TIME_FIELD) >=7
Ronaldo Cano
  • 905
  • 8
  • 19
  • I don't think that will work Ronaldo, there needs to be some element of iteration to cover all the days that might be present in the other table... the other table is not accesible to me everyday, it is given as table dumps. – beliskna Aug 09 '17 at 20:51
  • the select statement will return a dataset with all the rows you need, i think it is not necessary to build a loop – Ronaldo Cano Aug 09 '17 at 21:05
  • And how will it work when the other table contains let's say 10 days worth of information? Without a loop or similar the new table will only contain the last date plus the next day. – beliskna Aug 09 '17 at 21:24
  • in that case you need a task to execute the insert into the new table, you can schedule the task maybe every hour of the day, you could use a SQL Server Job to accomplish this – Ronaldo Cano Aug 09 '17 at 21:51
  • Yes. I agree that method could work, but I was hoping to do everything in bulk using one query that would run once every time the data dump happened. I'm thinking ahead to running this query on the archived data where there will be thousands of days to summarise. – beliskna Aug 09 '17 at 22:14
  • if you have access to modify the source database, maybe you can create a trigger that inserts the data into your new table every time a row is inserted into the source table – Ronaldo Cano Aug 09 '17 at 22:40
  • No. I don't have access and many rows in the source are summarised before adding to the destination... one record in the destination comes from summarising hundreds of records from the source. – beliskna Aug 10 '17 at 06:13