How to send+update on duplicate a big time series to SQL server

Question

I use a SQL Server 2008 I would like to send (and update on duplicate datetime index) a big time series (more than 1000 datapoints and eventually much more than that). What is the best way to do so? I have been advised to use

MERGE

and

BULK INSERT

but,

1. can we use the 2 at the same time?
2. I don't want to save my data first to a file. It is already in memory, why should I have to save it first to a file? Plus I can have several threads calling this method at the same time and it is going to be a bit messy.

Any advice welcome

Answer 1

If the data is in memory, you can use SQLBulkCOpy in .net or similar to send data to SQL Server. No need to instantiate a file.

And load a staging table first in SQL Server. Then use MERGE from this staging table to the actual table

If you don't want a persistent staging table, create a #temp table and use that in the subsequent MERGE. I'm not sure about the usefulness of Table Valued Parameters here

If you want to load the final table directly, then you can MERGE directly from a file using OPENROWSET(BULK…). But you don't want to instantiate a file.

In summary: there is no direct "in client memory" to final table solution: you need an intermediate table or file

Answer 2

• MERGE performs inserts as part of its core functionality, so the amount of additional work required to get it to work with BULK INSERT may not make it worth your while to attempt to use both. In summary, MERGE works by comparing data on a user specified column(s) and rules then inserting or updating records from the source table into the target table depending on that comparison.

  If you are not going to use MERGE you will need to have seperate UPDATE and INSERT statements. Please be aware that MERGE is only available in SQL Server 2008 and R2.

  More info is available here: http://msdn.microsoft.com/en-us/library/bb510625.aspx

• To answer your 2nd question accurately you will need to specify how the data to be imported is currently being stored. Please describe if it's being stored in a database server, as a file, etc. This will also impact on your ability to implement a hybrid merge \ bulk insert solution.

Answer 3

You talk about your data already being in memory - I assume this is in the memory of your application and so still needs to be submitted to the SQL Server. You also mention many threads wanting to do the same thing. I would use MERGE or a combination of INSERT/UPDATEs. In order to use MERGE, you will need to submit this data first to a source table, and then use the MERGE to add these records to the target table. Depending on frequency and size of updates you will need to think about the locking situations that may occur (or threads waiting for transactions to complete) when updating the source table. You may have less locking issues with simply using series of UPDATEs and INSERTs (possibly in a stored proc). It's difficult to know without knowing more about the application requirements.