Re: Please help how to Delete records with a MSSQL Command based on parameters.
- 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 firstname.lastname@example.org, Herro Wijnholds <h.wijnholds@...> wrote:
> Hi Ruben,
> 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]