1

Scenario : I have a collection of records to be inserted/updated into a table. Each record before getting inserted/updated in the table must be checked with the other rows in the table so as to find whether the same record already exists.

Currently used logic : Used the concept of cursor in an SQL server stored procedure to compare each record with every other record in the table before insertion/updation.

Limitations : As the number of records increases in the target table, performance is considerably decreased.

Can I get a better solution than what I have used?

| improve this question | | | | |
  • Without looking at code it would not be good to suggest but consider changing cursor to set based logic. Probably while loop would be correct replacement. – Shanky Oct 6 '15 at 7:32
  • 1
    Why not use a unique constraint for integrity and handle the violations in error handling (simple-talk.com/sql/t-sql-programming/…) – Spörri Oct 6 '15 at 10:32
  • Show us your table definitions and code. Without it we're guessing. – Colin 't Hart Oct 6 '15 at 11:40
  • But, in general, for high-performance you'll do better to switch to set-based processing rather than row-by-row, aka slow-by-slow. – Colin 't Hart Oct 6 '15 at 11:49
0

Have you looked at using the Merge command, this was introduced in SQL Server 2008 and does an update or insert based on your matching criteria.

I use this for merging XML quite a lot and it seems very efficient.

MERGE [Database] AS TargetTABLE
USING (SELECT @Var1 AS [Value1]
    @Var2 AS [Value2], 
    @Var3 AS [Value3]) AS SourceTABLE
ON TargetTABLE.[id] = SourceTABLE.[Value1]
WHEN MATCHED THEN
    UPDATE SET TargetTABLE.[Value2] = SourceTABLE.[Value2],
        TargetTABLE.[Value3] = SourceTABLE.[Value3]
WHEN NOT MATCHED THEN
    INSERT (
        [id], 
        [Value1],
        [Value2],
        [Value3])
    VALUES (
        SourceTABLE.[Value1], 
        SourceTABLE.[Value2],
        SourceTABLE.[Value3]
    );

Merge Command

| improve this answer | | | | |

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