Cursor-Killing: Retrieving Recently Modified Data

  • Ed Pollack

    Hall of Fame

    Points: 3107

    Comments posted to this topic are about the item Cursor-Killing: Retrieving Recently Modified Data

  • Clinton Smith

    SSC Enthusiast

    Points: 136

    Thanks for this it is very useful and explains something I came across in someone else's TSQL a few years back.

    I assume there is something similar for UPDATED?

    I haven't had a chance to check myself.

  • Ed Pollack

    Hall of Fame

    Points: 3107

    You can use this same syntax for updates, but there are only INSERTED and DELETED tables (there is no UPDATED). For all intensive purposes, an update is a combination of a delete followed by an insert.

    To return changed data in an UPDATE statement, you can use DELETED to return rows of data before the update was performed, and INSERTED for those same rows after the update. If you need additional data from your SELECT, you can use MERGE to bring everything back (see the last example).

    It's a bit dry, but the MSDN article on the OUTPUT clause is both comprehensive & helpful:

    http://msdn.microsoft.com/en-us/library/ms177564.aspx

  • hfreeman

    SSC-Addicted

    Points: 496

    Edward, thanks for such a well WRITTEN article. You have raised the bar for others who post at SQL Server Central.com.

    Five Stars !

    Hank Freeman

    SQL server DBA/Data Architect - Atlanta, GA

    :smooooth:

    Hank Freeman
    Senior SQL Server DBA / Data & Solutions Architect
    hfreeman@msn.com
    678-414-0090 (Personal Cell)

  • halifaxdal

    SSCoach

    Points: 19584

    Where can I download the database? All I found are for newer version like 2008R2 or DW2008R2

    Thanks.

  • Ed Pollack

    Hall of Fame

    Points: 3107

    halifaxdal (6/2/2014)


    Where can I download the database? All I found are for newer version like 2008R2 or DW2008R2

    Thanks.

    The scripts here were tested with Adventureworks2008 as well as AdventureWorks2012---you can use either and expect similar results. The MERGE statement will only work on SQL Server 2008 and later, though.

    Here's a link to the Codeplex site with download links for both versions (depending on your version of SQL Server):

    http://msftdbprodsamples.codeplex.com/releases/view/93587

  • halifaxdal

    SSCoach

    Points: 19584

    Ed Pollack (6/2/2014)


    halifaxdal (6/2/2014)


    Where can I download the database? All I found are for newer version like 2008R2 or DW2008R2

    Thanks.

    The scripts here were tested with Adventureworks2008 as well as AdventureWorks2012---you can use either and expect similar results. The MERGE statement will only work on SQL Server 2008 and later, though.

    Here's a link to the Codeplex site with download links for both versions (depending on your version of SQL Server):

    http://msftdbprodsamples.codeplex.com/releases/view/93587

    Thanks.

    I just ran the first script in Adventureworks2008 and I got:

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R93R-62-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R93R-44-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R93R-48-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R93R-52-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R93R-56-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M82S-38-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M82S-42-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M82S-44-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M82S-48-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M82B-38-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M82B-42-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M82B-44-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M82B-48-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M68S-38-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M68S-42-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M68S-46-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M68B-38-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M68B-42-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M68B-46-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R89R-44-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R89R-48-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R89R-52-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R89R-58-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R89B-44-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R89B-48-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R89B-52-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R89B-58-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-T79Y-46-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-T79Y-50-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-T79Y-54-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-T79Y-60-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-T79U-46-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-T79U-50-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-T79U-54-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-T79U-60-N).

    The statement has been terminated.

    I also ran it in AdventureWorksDW2008R2 and I got:

    Msg 208, Level 16, State 1, Line 5

    Invalid object name 'Production.Product'.

  • Ed Pollack

    Hall of Fame

    Points: 3107

    halifaxdal (6/2/2014)


    Ed Pollack (6/2/2014)


    halifaxdal (6/2/2014)


    Where can I download the database? All I found are for newer version like 2008R2 or DW2008R2

    Thanks.

    The scripts here were tested with Adventureworks2008 as well as AdventureWorks2012---you can use either and expect similar results. The MERGE statement will only work on SQL Server 2008 and later, though.

    Here's a link to the Codeplex site with download links for both versions (depending on your version of SQL Server):

    http://msftdbprodsamples.codeplex.com/releases/view/93587

    Thanks.

    I just ran the first script in Adventureworks2008 and I got:

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R93R-62-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R93R-44-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R93R-48-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R93R-52-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R93R-56-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M82S-38-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M82S-42-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M82S-44-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M82S-48-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M82B-38-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M82B-42-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M82B-44-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M82B-48-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M68S-38-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M68S-42-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M68S-46-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M68B-38-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M68B-42-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-M68B-46-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R89R-44-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R89R-48-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R89R-52-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R89R-58-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R89B-44-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R89B-48-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R89B-52-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-R89B-58-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-T79Y-46-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-T79Y-50-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-T79Y-54-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-T79Y-60-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-T79U-46-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-T79U-50-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-T79U-54-N).

    The statement has been terminated.

    Msg 2601, Level 14, State 1, Line 19

    Cannot insert duplicate key row in object 'Production.Product' with unique index 'AK_Product_ProductNumber'. The duplicate key value is (BK-T79U-60-N).

    The statement has been terminated.

    I also ran it in AdventureWorksDW2008R2 and I got:

    Msg 208, Level 16, State 1, Line 5

    Invalid object name 'Production.Product'.

    The error on AdventureWorksDW2008R2 is expected as that is the data warehouse sample database, and has a different schema from the standard AdventureWorks database.

    The error you're receiving on Adventureworks2008 is the result of a unique index on ProductNumber. When we insert the data, I set the new ProductNumber equal to ProductNumber + '-N', which ensures that any new data we create will be similar, but unique. If you run the script multiple times, you'll end up with duplicates.

    By default, there are no ProductNumbers in AdventureWorks that end in the letter N, so you can quickly delete our new data, and then run the script again successfully with a delete like this:

    DELETE FROM Production.Product

    WHERE ProductNumber LIKE '%N'

    This should delete our newly created 35 rows and allow us to avoid violating unique indexes on the Product table.

  • Bruce W Cassidy

    SSChampion

    Points: 12607

    Another way to deal with this issue is by using sequences to pre-load the key values (instead of using identity). You can create a default on the surrogate key column which also references the same sequence, so it will still behave in much the same way as an identity, but still give you the flexibility of block allocating key values ahead of time. This is really useful when you are assembling a parent-child set in preparation for inserting.

    In most cases though, I've not had to do this. If there's an alternate key on the parent (which is almost always), I can join to the parent by that when I am inserting the children to pick up the surrogate key for the parent.

    I totally agree that the row-by-row approach is a dreadful way to tackle the issue.

  • aochss

    SSCommitted

    Points: 1677

    Ed,

    Thanks for the article and thanks for yet another nugget that is well hidden in the "dry" MSDN atricles. This will help me out tremendously when doing data manipulation changes in the future.

    Another reason why SQL Server Central is the best place to find out new and "new to me" features of SQL Server without all of the restrictions of some other tech sites (Stackoverflow).

    With your "newish" rating and Clinton's "very newish" rating, this article any any of Clinton's follow up questions would not be allowed on the SO sites since you do not have the right amount of reputation points or even better, not asked good enough questions to be allowed to up-vote or add something quite beneficial to the site.

    Thanks again SSC and it's wonderful users,

    Anton

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply