MERGE SQL Server Primary Key Violation
0
is there any chance that I can execute the below sql statement successfully?
Currently, I'm receiving Primary Key Violation on my query below.
What I want is that, when the first record was inserted in the target table and if there is another same primary key that will be inserted, it should be execute an UPDATE not INSERT to avoid the primary key violation, but I don't know to write it in actual sql script. As of know, I only have the below script.
// User-Defined Tabled Type
DECLARE @tvpEmailType dbo.EmailType
INSERT @tvpEmailType VALUES ('mail@mail.com', 1)
INSERT @tvpEmailType VALUES ('mail@mail.com', 0)
MERGE dbo.EmailRepo AS TARGET
USING (SELECT DISTINCT * FROM @tvpEmailType) AS SOURCE
ON (TARGET.Email = SOURCE.Email)
WHEN MATCHED AND TARGET.Status <> SOURCE.Status THEN
UPDATE SET TARGET.Status = SOURCE.Status
WHEN NOT MATCHED THEN
INSERT (Email, Status) VALUES (SOURCE.Email, SOURCE.Status);
sql-server tsql primary-key asked May 28 '18 at 4:06
klaydzeklaydze
538618
add a comment |
0
is there any chance that I can execute the below sql statement successfully?
Currently, I'm receiving Primary Key Violation on my query below.
What I want is that, when the first record was inserted in the target table and if there is another same primary key that will be inserted, it should be execute an UPDATE not INSERT to avoid the primary key violation, but I don't know to write it in actual sql script. As of know, I only have the below script.
// User-Defined Tabled Type
DECLARE @tvpEmailType dbo.EmailType
INSERT @tvpEmailType VALUES ('mail@mail.com', 1)
INSERT @tvpEmailType VALUES ('mail@mail.com', 0)
MERGE dbo.EmailRepo AS TARGET
USING (SELECT DISTINCT * FROM @tvpEmailType) AS SOURCE
ON (TARGET.Email = SOURCE.Email)
WHEN MATCHED AND TARGET.Status <> SOURCE.Status THEN
UPDATE SET TARGET.Status = SOURCE.Status
WHEN NOT MATCHED THEN
INSERT (Email, Status) VALUES (SOURCE.Email, SOURCE.Status);
asked May 28 '18 at 4:06
klaydzeklaydze
538618
2
That really depends on the definition of your primary key. If it's not defined onEmailAddressthen you're out of luck
– Nick.McDermaid
May 28 '18 at 4:08
EmailAddress field on Target table 'dbo.EmailRepo' is the primary key
– klaydze
May 28 '18 at 4:18
Oh now I see - you have duplicate records with a different status. Firstly, there is no order to the records you supply. I know you suppliedstatus=0second, but that doesn't mean anything in a relational database. To do this you need to supply an order key in the source data, then you need to change the SQL in theSOURCEsubtable to pick the correct single final record based on the order key. This source dataset would also be a problem if you usedUPDATE/INSERTas well.
– Nick.McDermaid
May 28 '18 at 4:21
add a comment |
0
0
0
is there any chance that I can execute the below sql statement successfully?
Currently, I'm receiving Primary Key Violation on my query below.
What I want is that, when the first record was inserted in the target table and if there is another same primary key that will be inserted, it should be execute an UPDATE not INSERT to avoid the primary key violation, but I don't know to write it in actual sql script. As of know, I only have the below script.
// User-Defined Tabled Type
DECLARE @tvpEmailType dbo.EmailType
INSERT @tvpEmailType VALUES ('mail@mail.com', 1)
INSERT @tvpEmailType VALUES ('mail@mail.com', 0)
MERGE dbo.EmailRepo AS TARGET
USING (SELECT DISTINCT * FROM @tvpEmailType) AS SOURCE
ON (TARGET.Email = SOURCE.Email)
WHEN MATCHED AND TARGET.Status <> SOURCE.Status THEN
UPDATE SET TARGET.Status = SOURCE.Status
WHEN NOT MATCHED THEN
INSERT (Email, Status) VALUES (SOURCE.Email, SOURCE.Status);
asked May 28 '18 at 4:06
klaydzeklaydze
538618
is there any chance that I can execute the below sql statement successfully?
Currently, I'm receiving Primary Key Violation on my query below.
What I want is that, when the first record was inserted in the target table and if there is another same primary key that will be inserted, it should be execute an UPDATE not INSERT to avoid the primary key violation, but I don't know to write it in actual sql script. As of know, I only have the below script.
// User-Defined Tabled Type
DECLARE @tvpEmailType dbo.EmailType
INSERT @tvpEmailType VALUES ('mail@mail.com', 1)
INSERT @tvpEmailType VALUES ('mail@mail.com', 0)
MERGE dbo.EmailRepo AS TARGET
USING (SELECT DISTINCT * FROM @tvpEmailType) AS SOURCE
ON (TARGET.Email = SOURCE.Email)
WHEN MATCHED AND TARGET.Status <> SOURCE.Status THEN
UPDATE SET TARGET.Status = SOURCE.Status
WHEN NOT MATCHED THEN
INSERT (Email, Status) VALUES (SOURCE.Email, SOURCE.Status);
asked May 28 '18 at 4:06
klaydzeklaydze
538618
asked May 28 '18 at 4:06
klaydzeklaydze
538618
edited May 28 '18 at 4:17
klaydze
asked May 28 '18 at 4:06
klaydzeklaydze
538618
asked May 28 '18 at 4:06
klaydzeklaydze
538618
asked May 28 '18 at 4:06
klaydzeklaydze
538618
538618
2
That really depends on the definition of your primary key. If it's not defined onEmailAddressthen you're out of luck
– Nick.McDermaid
May 28 '18 at 4:08
EmailAddress field on Target table 'dbo.EmailRepo' is the primary key
– klaydze
May 28 '18 at 4:18
Oh now I see - you have duplicate records with a different status. Firstly, there is no order to the records you supply. I know you suppliedstatus=0second, but that doesn't mean anything in a relational database. To do this you need to supply an order key in the source data, then you need to change the SQL in theSOURCEsubtable to pick the correct single final record based on the order key. This source dataset would also be a problem if you usedUPDATE/INSERTas well.
– Nick.McDermaid
May 28 '18 at 4:21
add a comment |
2
That really depends on the definition of your primary key. If it's not defined onEmailAddressthen you're out of luck
– Nick.McDermaid
May 28 '18 at 4:08
EmailAddress field on Target table 'dbo.EmailRepo' is the primary key
– klaydze
May 28 '18 at 4:18
Oh now I see - you have duplicate records with a different status. Firstly, there is no order to the records you supply. I know you suppliedstatus=0second, but that doesn't mean anything in a relational database. To do this you need to supply an order key in the source data, then you need to change the SQL in theSOURCEsubtable to pick the correct single final record based on the order key. This source dataset would also be a problem if you usedUPDATE/INSERTas well.
– Nick.McDermaid
May 28 '18 at 4:21
2
2
That really depends on the definition of your primary key. If it's not defined on
EmailAddress then you're out of luck– Nick.McDermaid
May 28 '18 at 4:08
That really depends on the definition of your primary key. If it's not defined on
EmailAddress then you're out of luck– Nick.McDermaid
May 28 '18 at 4:08
EmailAddress field on Target table 'dbo.EmailRepo' is the primary key
– klaydze
May 28 '18 at 4:18
EmailAddress field on Target table 'dbo.EmailRepo' is the primary key
– klaydze
May 28 '18 at 4:18
Oh now I see - you have duplicate records with a different status. Firstly, there is no order to the records you supply. I know you supplied
status=0 second, but that doesn't mean anything in a relational database. To do this you need to supply an order key in the source data, then you need to change the SQL in the SOURCE subtable to pick the correct single final record based on the order key. This source dataset would also be a problem if you used UPDATE/INSERT as well.– Nick.McDermaid
May 28 '18 at 4:21
Oh now I see - you have duplicate records with a different status. Firstly, there is no order to the records you supply. I know you supplied
status=0 second, but that doesn't mean anything in a relational database. To do this you need to supply an order key in the source data, then you need to change the SQL in the SOURCE subtable to pick the correct single final record based on the order key. This source dataset would also be a problem if you used UPDATE/INSERT as well.– Nick.McDermaid
May 28 '18 at 4:21
add a comment |
1 Answer
1
active
oldest
votes
1
Bingo
DECLARE @i table (iden int identity, email varchar(40), status bit);
DECLARE @t table (email varchar(40) primary key, status bit);
INSERT @i VALUES ('mail@mail.com', 1), ('mail@mail.com', 0)
MERGE @t AS TARGET
USING ( select email, status
from ( select email, status
, row_number() over (partition by email order by iden desc) as rn
from @i
) t
where t.rn = 1
) AS SOURCE
ON TARGET.Email = SOURCE.Email
WHEN MATCHED THEN
UPDATE SET TARGET.Status = SOURCE.Status
WHEN NOT MATCHED THEN
INSERT (Email, Status) VALUES (SOURCE.Email, SOURCE.Status);
select * from @t
answered May 28 '18 at 5:00
paparazzopaparazzo
37.5k1673137
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f50558982%2fmerge-sql-server-primary-key-violation%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Bingo
DECLARE @i table (iden int identity, email varchar(40), status bit);
DECLARE @t table (email varchar(40) primary key, status bit);
INSERT @i VALUES ('mail@mail.com', 1), ('mail@mail.com', 0)
MERGE @t AS TARGET
USING ( select email, status
from ( select email, status
, row_number() over (partition by email order by iden desc) as rn
from @i
) t
where t.rn = 1
) AS SOURCE
ON TARGET.Email = SOURCE.Email
WHEN MATCHED THEN
UPDATE SET TARGET.Status = SOURCE.Status
WHEN NOT MATCHED THEN
INSERT (Email, Status) VALUES (SOURCE.Email, SOURCE.Status);
select * from @t
answered May 28 '18 at 5:00
paparazzopaparazzo
37.5k1673137
add a comment |
1
Bingo
DECLARE @i table (iden int identity, email varchar(40), status bit);
DECLARE @t table (email varchar(40) primary key, status bit);
INSERT @i VALUES ('mail@mail.com', 1), ('mail@mail.com', 0)
MERGE @t AS TARGET
USING ( select email, status
from ( select email, status
, row_number() over (partition by email order by iden desc) as rn
from @i
) t
where t.rn = 1
) AS SOURCE
ON TARGET.Email = SOURCE.Email
WHEN MATCHED THEN
UPDATE SET TARGET.Status = SOURCE.Status
WHEN NOT MATCHED THEN
INSERT (Email, Status) VALUES (SOURCE.Email, SOURCE.Status);
select * from @t
answered May 28 '18 at 5:00
paparazzopaparazzo
37.5k1673137
add a comment |
1
1
1
Bingo
DECLARE @i table (iden int identity, email varchar(40), status bit);
DECLARE @t table (email varchar(40) primary key, status bit);
INSERT @i VALUES ('mail@mail.com', 1), ('mail@mail.com', 0)
MERGE @t AS TARGET
USING ( select email, status
from ( select email, status
, row_number() over (partition by email order by iden desc) as rn
from @i
) t
where t.rn = 1
) AS SOURCE
ON TARGET.Email = SOURCE.Email
WHEN MATCHED THEN
UPDATE SET TARGET.Status = SOURCE.Status
WHEN NOT MATCHED THEN
INSERT (Email, Status) VALUES (SOURCE.Email, SOURCE.Status);
select * from @t
answered May 28 '18 at 5:00
paparazzopaparazzo
37.5k1673137
Bingo
DECLARE @i table (iden int identity, email varchar(40), status bit);
DECLARE @t table (email varchar(40) primary key, status bit);
INSERT @i VALUES ('mail@mail.com', 1), ('mail@mail.com', 0)
MERGE @t AS TARGET
USING ( select email, status
from ( select email, status
, row_number() over (partition by email order by iden desc) as rn
from @i
) t
where t.rn = 1
) AS SOURCE
ON TARGET.Email = SOURCE.Email
WHEN MATCHED THEN
UPDATE SET TARGET.Status = SOURCE.Status
WHEN NOT MATCHED THEN
INSERT (Email, Status) VALUES (SOURCE.Email, SOURCE.Status);
select * from @t
answered May 28 '18 at 5:00
paparazzopaparazzo
37.5k1673137
answered May 28 '18 at 5:00
paparazzopaparazzo
37.5k1673137
answered May 28 '18 at 5:00
paparazzopaparazzo
37.5k1673137
answered May 28 '18 at 5:00
paparazzopaparazzo
37.5k1673137
37.5k1673137
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f50558982%2fmerge-sql-server-primary-key-violation%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
That really depends on the definition of your primary key. If it's not defined on
EmailAddressthen you're out of luck– Nick.McDermaid
May 28 '18 at 4:08
EmailAddress field on Target table 'dbo.EmailRepo' is the primary key
– klaydze
May 28 '18 at 4:18
Oh now I see - you have duplicate records with a different status. Firstly, there is no order to the records you supply. I know you supplied
status=0second, but that doesn't mean anything in a relational database. To do this you need to supply an order key in the source data, then you need to change the SQL in theSOURCEsubtable to pick the correct single final record based on the order key. This source dataset would also be a problem if you usedUPDATE/INSERTas well.– Nick.McDermaid
May 28 '18 at 4:21