Loading ...
Sorry, an error occurred while loading the content.

Re: Please help how to Delete records with a MSSQL Command based on parameters.

Expand Messages
  • FRED
    Since FECHA_DOCMTO is probably a DateTime filed on MSSQL, better to choose an universal DT format, like YYYYMMDD, ie: FECHA_DOCMTO = :1 Argument #1: DStr(
    Message 1 of 3 , Apr 1, 2013
    • 0 Attachment
      Since "FECHA_DOCMTO" is probably a DateTime filed on MSSQL, better to choose an universal DT format, like YYYYMMDD, ie:

      FECHA_DOCMTO = ':1'

      Argument #1: DStr( <yourDate>, 'YYYYMMDD' )

      --- In magicu-l@yahoogroups.com, Herro Wijnholds <h.wijnholds@...> wrote:
      >
      > Hi Ruben,
      >
      > Try:
      >
      >
      > DELETE FROM dbo.CI_DET_MOVMTOS_INV WHERE FECHA_DOCMTO BETWEEN ':1' AND ':2'
      >
      >
      > :1 = First parameter (date from)
      > :2 = Second parameter (date to)
      >
      >
      >
      >
      > /Best Regards,
      > Herro Wijnholds
      > ///
      >
      > Op 31-3-2013 10:33, RUBEN FIGUEROA schreef:
      > >
      > > Greetings.
      > > Dearest Group,
      > > I'm developing a system that needs to maintain a large amount of
      > > records stored into a MSSQL 2000 table, due data maintenance we have
      > > the need to delete by range large portions of the transactional data,
      > > sometimes a whole year or a month, sometimes only a date. The section
      > > of insert the new records I've done with a Link Create / Open Reindex
      > > and the records are stored/populated very fast, but when it means to
      > > previously delete the range I'm using the Magic Engine, Batch /
      > > Delete, Open Fast, to do so basically I keep a file that holds two
      > > records the Date-from and Date-to, I'm using this table to delete/ add
      > > according to these dates,
      > > Due to we have now a large amount of detail records, the process takes
      > > hours just to delete, and I've seen some posts regarding to the
      > > efficient use of Direct SQL statement inside a Magic program to solve
      > > a situation like this, so far I've tried is much faster deleting using
      > > Direct SQL Rather using a batch Tsk from Magic engine. I don't know
      > > how to combine the two files in the magic task in order to delete in a
      > > range from the fields of date given from another file.
      > > In Magic I just created a sample program in Batch, End task = Yes /
      > > Evaluate = After
      > > With in the SQL Command sentence:
      > >
      > > DELETE FROM dbo.CI_DET_MOVMTOS_INV WHERE FECHA_DOCMTO BETWEEN
      > > '20050101' AND '20051231'
      > >
      > > Executing this program deletes in seconds the data for that range. But
      > > I need to find a way to pass the parameters '20050101' and '20051231'
      > > (The dates stored in the file of dates from/to) and need to use both
      > > fields to compose the SQL argument. So far I've tried for hours with
      > > no results.
      > > Thanks in advance I'll appreciate if you can help me.
      > >
      > > Regards,
      > > Ruben
      > > rubenfigueroa@... <mailto:rubenfigueroa%40yahoo.com>
      > >
      > >
      >
      >
      >
      > [Non-text portions of this message have been removed]
      >
    Your message has been successfully submitted and would be delivered to recipients shortly.