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?

closed as unclear what you're asking by Mark Sinkinson, Philᵀᴹ, Colin 't Hart, Kin Shah, Erwin Brandstetter Oct 6 '15 at 13:37

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the 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

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