Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

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

share|improve this answer
    
Thanks that's very clear now –  RockScience Sep 28 '11 at 8:30
  • 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.

share|improve this answer
    
by saying "you will need to have seperate UPDATE and INSERT statements.", do you mean I need to separate the data myself and create 2 sql queries, or can it be done inside one unique query? –  RockScience Sep 28 '11 at 7:28
    
What I meant was that you'd have to write an update query to update existing rows on your matching condition, then your bulk insert query to insert rows from the source data that have no match in the target data on your matching condition. You could then execute these in the same batch. –  WT_W Sep 28 '11 at 7:38

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.