Can this be done in SQL?

Every 30 seconds, 400K recs are imported to a table. At the end of 30 secs,
the 400K recs are deleted and another 400K recs are imported. This needs to
happen while clients are connected and are retrieving recs continuously.


SQL 2000

Thx,
Don

To truncate a table you need a table level lock and you can not have any RI.
If there are brief periods in which you can get the tab lock then yes. But
what happens to the users who query immediately after you truncate? Sounds
like the design is flawed and should be looked at for a cleaner solution.
Have you thought about partitioning?

What is partitioning?


"Andrew J. Kelly" wrote:
[..]

Partitioning the data in the table over several tables (2000) or having several partitions for the
same table (2005). Basically, you let a value in a column decide what table or partition the data
should be stored. Of you say what version you are using, we can point you to what search words to
search in BOL for.

How different are the 400k new rows from the 400k old rows? This
reminds me of thinking it is easier to replace the entire table than
to identify and apply specific changes - which may very well be true,
but what you describe is pretty severe. How long are the rows?

Importing 400k rows isn't going to happen in an instant, particularly
if there are indexes to make it possible to USE those rows, and with
400k rows and 30 seconds you had better have indexes. While table
truncation is very fast, freeing up the pages for re-use happens after
that and might not keep up to the rate you proipose, causing the data
space to grow as long as the process runs.

Roy Harvey
Beacon Falls, CT

On Tue, 24 Jul 2007 13:02:06 -0700, donsql22222
<donsql22222> wrote:
[..]