May 31, 2014 at 9:03 pm
Comments posted to this topic are about the item Cursor-Killing: Retrieving Recently Modified Data
June 2, 2014 at 1:53 am
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.
June 2, 2014 at 6:17 am
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:
June 2, 2014 at 7:56 am
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)
June 2, 2014 at 8:44 am
Where can I download the database? All I found are for newer version like 2008R2 or DW2008R2
Thanks.
June 2, 2014 at 9:04 am
halifaxdal (6/2/2014)
Where can I download the database? All I found are for newer version like 2008R2 or DW2008R2Thanks.
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):
June 2, 2014 at 9:16 am
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 DW2008R2Thanks.
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):
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'.
June 2, 2014 at 9:34 am
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 DW2008R2Thanks.
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):
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.
June 2, 2014 at 5:50 pm
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.
June 3, 2014 at 12:03 pm
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply