December 29, 2017 at 1:28 pm
Also its worth noting that Lynn's posted solution will may not work under some high workload conditions, unless you are using a serializable compatible isolation mode or hint, or using a retry loop on insert failures. I was pointed into this direction by Gail's post (which I can't find right now unfortunately) so its not just me making stuff up LOL
here's some reading to get you started!
https://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there
http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx
December 29, 2017 at 1:48 pm
This one? http://source.entelect.co.za/why-is-this-upsert-code-broken
It's my company's showcase site, which is probably why you couldn't find it. I do have a similar post that's still in draft on my blog.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2017 at 1:57 pm
patrickmcginnis59 10839 - Friday, December 29, 2017 1:28 PMAlso its worth noting that Lynn's posted solutionwillmay not work under some high workload conditions, unless you are using a serializable compatible isolation mode or hint, or using a retry loop on insert failures. I was pointed into this direction by Gail's post (which I can't find right now unfortunately) so its not just me making stuff up LOLhere's some reading to get you started!
https://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there
http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx
My code looks nothing like the code in the article in Gail's link. We would need to do some testing to determine if it may fail.
December 29, 2017 at 2:03 pm
Lynn Pettis - Friday, December 29, 2017 1:57 PMpatrickmcginnis59 10839 - Friday, December 29, 2017 1:28 PMAlso its worth noting that Lynn's posted solutionwillmay not work under some high workload conditions, unless you are using a serializable compatible isolation mode or hint, or using a retry loop on insert failures. I was pointed into this direction by Gail's post (which I can't find right now unfortunately) so its not just me making stuff up LOLhere's some reading to get you started!
https://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there
http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx
My code looks nothing like the code in the article in Gail's link. We would need to do some testing to determine if it may fail.
Look at the very last example and paragraph in the article.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2017 at 2:14 pm
GilaMonster - Friday, December 29, 2017 2:03 PMLynn Pettis - Friday, December 29, 2017 1:57 PMpatrickmcginnis59 10839 - Friday, December 29, 2017 1:28 PMAlso its worth noting that Lynn's posted solutionwillmay not work under some high workload conditions, unless you are using a serializable compatible isolation mode or hint, or using a retry loop on insert failures. I was pointed into this direction by Gail's post (which I can't find right now unfortunately) so its not just me making stuff up LOLhere's some reading to get you started!
https://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there
http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx
My code looks nothing like the code in the article in Gail's link. We would need to do some testing to determine if it may fail.
Look at the very last example and paragraph in the article.
I stand corrected.
December 29, 2017 at 2:19 pm
Lynn Pettis - Friday, December 29, 2017 2:14 PMGilaMonster - Friday, December 29, 2017 2:03 PMLynn Pettis - Friday, December 29, 2017 1:57 PMpatrickmcginnis59 10839 - Friday, December 29, 2017 1:28 PMAlso its worth noting that Lynn's posted solutionwillmay not work under some high workload conditions, unless you are using a serializable compatible isolation mode or hint, or using a retry loop on insert failures. I was pointed into this direction by Gail's post (which I can't find right now unfortunately) so its not just me making stuff up LOLhere's some reading to get you started!
https://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there
http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx
My code looks nothing like the code in the article in Gail's link. We would need to do some testing to determine if it may fail.
Look at the very last example and paragraph in the article.
I stand corrected.
It surprised the hell out of me when I was testing queries for that article.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 31, 2017 at 2:59 pm
Are there only 2 columns in dbo.Tbl_ComputerInventory?
_____________
Code for TallyGenerator
January 3, 2018 at 10:36 am
I'm sure ya'll season SQL users understand Lynn's ideal, however for this newbie, I have no clue what her [ci2] or [ci1] refers too and can only guess the "Select 1" is some sort of syntax that tells SQL to look at one record at a time. I tried for grins as is with no luck.
The last example on https://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there has a last End statement I don't know what started it and indicates a condition I'm not looking for in my ImpTbl. :ermm:
January 3, 2018 at 10:41 am
SELECT 1 means just that. Select the literal value 1. It's used in EXISTS because SQL doesn't care about the columns in an EXISTS. You could replace it with SELECT 'Whatever' FROM <rest of statement> with equal effect.
ci2 and ci1 are aliases (names) for tables.
FROM TableName AS Alias
then you refer to the alias elsewhere. It's to reduce typing.
The last example on that page (which is a very bad answer, and only works for a single row. Don't use it), the END is 2 rows below the BEGIN that it matches.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2018 at 11:03 am
shannonphillips - Wednesday, January 3, 2018 10:36 AMI'm sure ya'll season SQL users understand Lynn's ideal, however for this newbie, I have no clue what her [ci2] or [ci1] refers too and can only guess the "Select 1" is some sort of syntax that tells SQL to look at one record at a time. I tried for grins as is with no luck.The last example on https://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there has a last End statement I don't know what started it and indicates a condition I'm not looking for in my ImpTbl. :ermm:
Don't assume I am a her by name, I'm not.
January 3, 2018 at 11:04 am
GilaMonster - Wednesday, January 3, 2018 10:41 AMSELECT 1 means just that. Select the literal value 1. It's used in EXISTS because SQL doesn't care about the columns in an EXISTS. You could replace it with SELECT 'Whatever' FROM <rest of statement> with equal effect.ci2 and ci1 are aliases (names) for tables.
FROM TableName AS Alias
then you refer to the alias elsewhere. It's to reduce typing.The last example on that page (which is a very bad answer, and only works for a single row. Don't use it), the END is 2 rows below the BEGIN that it matches.
Hmm, I seem to remember posting earlier that ci2 and ci1 are table aliases.
January 3, 2018 at 11:05 am
shannonphillips - Wednesday, January 3, 2018 10:36 AMI'm sure ya'll season SQL users understand Lynn's ideal, however for this newbie, I have no clue what her [ci2] or [ci1] refers too and can only guess the "Select 1" is some sort of syntax that tells SQL to look at one record at a time. I tried for grins as is with no luck.The last example on https://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there has a last End statement I don't know what started it and indicates a condition I'm not looking for in my ImpTbl. :ermm:
When you say you had no luck, what happened or didn't happen? Sorry, but saying no luck provides absolutely no information. It is like going to a mechanic with your car and simply saying it's broke.
January 3, 2018 at 11:46 am
Lynn Pettis - Wednesday, January 3, 2018 11:05 AMshannonphillips - Wednesday, January 3, 2018 10:36 AMI'm sure ya'll season SQL users understand Lynn's ideal, however for this newbie, I have no clue what her [ci2] or [ci1] refers too and can only guess the "Select 1" is some sort of syntax that tells SQL to look at one record at a time. I tried for grins as is with no luck.The last example on https://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there has a last End statement I don't know what started it and indicates a condition I'm not looking for in my ImpTbl. :ermm:
When you say you had no luck, what happened or didn't happen? Sorry, but saying no luck provides absolutely no information. It is like going to a mechanic with your car and simply saying it's broke.
Insert INTO
[Tbl_ComputerInventory]
(
ISOCountryAbb3, SiteNumber, ComputerName, SerialNumber, Field5-20
)
SELECT DISTINCT
Left([Computer Name],3) AS ISOCountryAbb3, Right(Left([Computer Name],8),5) AS SiteNumber, [Computer Name] AS ComputerName, [Service Tag] AS SerialNumber, Fields5-20
FROM
[ImpTbl_ComputerInventory]
WHERE
NOT EXISTS (SELECT 1
FROM [Tbl_ComputerInventory]
WHERE ([ImpTbl_ComputerInventory].[Computer Name] = [Tbl_ComputerInventory].[ComputerName] AND [EDIImpTbl_ComputerInventory].[Service Tag] = [Tbl_ComputerInventory].[SerialNumber]));
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'Pkey_Tbl_ComputerInventory'. Cannot insert duplicate key in object 'dbo.Tbl_ComputerInventory'. The duplicate key value is (USA02403-UT1, MXL7381F8P).
January 3, 2018 at 11:53 am
That shouldn't even compile, because this does not exist as a table: [EDIImpTbl_ComputerInventory].[Service Tag]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2018 at 12:14 pm
shannonphillips - Wednesday, January 3, 2018 11:46 AMLynn Pettis - Wednesday, January 3, 2018 11:05 AMshannonphillips - Wednesday, January 3, 2018 10:36 AMI'm sure ya'll season SQL users understand Lynn's ideal, however for this newbie, I have no clue what her [ci2] or [ci1] refers too and can only guess the "Select 1" is some sort of syntax that tells SQL to look at one record at a time. I tried for grins as is with no luck.The last example on https://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there has a last End statement I don't know what started it and indicates a condition I'm not looking for in my ImpTbl. :ermm:
When you say you had no luck, what happened or didn't happen? Sorry, but saying no luck provides absolutely no information. It is like going to a mechanic with your car and simply saying it's broke.
Insert INTO
[Tbl_ComputerInventory]
(
ISOCountryAbb3, SiteNumber, ComputerName, SerialNumber, Field5-20
)
SELECT DISTINCT
Left([Computer Name],3) AS ISOCountryAbb3, Right(Left([Computer Name],8),5) AS SiteNumber, [Computer Name] AS ComputerName, [Service Tag] AS SerialNumber, Fields5-20
FROM
[ImpTbl_ComputerInventory]
WHERE
NOT EXISTS (SELECT 1
FROM [Tbl_ComputerInventory]
WHERE ([ImpTbl_ComputerInventory].[Computer Name] = [Tbl_ComputerInventory].[ComputerName] AND [EDIImpTbl_ComputerInventory].[Service Tag] = [Tbl_ComputerInventory].[SerialNumber]));
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'Pkey_Tbl_ComputerInventory'. Cannot insert duplicate key in object 'dbo.Tbl_ComputerInventory'. The duplicate key value is (USA02403-UT1, MXL7381F8P).
I would start by looking at the data in the two table where the ComputerName is 'USA02403-UT1' and the SerialNumber (or Service_Tag) is 'MXL7381F8P' and see if you can figure out why the SQL code is trying to insert it.
Also, is Fields5-20 an actual column or does it represent 15 generic columns? If the later it is possible your distinct isn't distinct enough and you may still have multiple rows of data for the same ComputerName/Service_Tag combination.
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply