SQL Server : continue inserting rows after exception
I have a stored procedure that does an insert like this one:
INSERT INTO [TargetTable] ([Hash], [Year], [Metadata])
SELECT [Hash], [Year], [Metadata]
FROM [SourceTable]
This query will fail because the [TargetTable] has data already and an unique index on the [Hash] column. It cannot insert duplicates.
How I can insert only the non-duplicates? I guess I could select first only the non-duplicates and then do the inserts. But the select would be more complex.
Another approach I tried is to ignore the duplicates. For instance:
BEGIN TRY
INSERT INTO [TargetTable] ([Hash], [Event], [Year], [Metadata])
SELECT [Hash], [Event], [Year], [Metadata]
FROM [SourceTable]
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH
I was expecting something like, try to insert this, if you cannot insert it, then ignore it, and then continue inserting the rest of rows.
sql
sql-server edited Nov 17 '18 at 14:25
marc_s
571k12811031252
asked Nov 17 '18 at 13:22
osotorrio
184314
add a comment |
I have a stored procedure that does an insert like this one:
INSERT INTO [TargetTable] ([Hash], [Year], [Metadata])
SELECT [Hash], [Year], [Metadata]
FROM [SourceTable]
This query will fail because the [TargetTable] has data already and an unique index on the [Hash] column. It cannot insert duplicates.
How I can insert only the non-duplicates? I guess I could select first only the non-duplicates and then do the inserts. But the select would be more complex.
Another approach I tried is to ignore the duplicates. For instance:
BEGIN TRY
INSERT INTO [TargetTable] ([Hash], [Event], [Year], [Metadata])
SELECT [Hash], [Event], [Year], [Metadata]
FROM [SourceTable]
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH
I was expecting something like, try to insert this, if you cannot insert it, then ignore it, and then continue inserting the rest of rows.
sql
sql-server edited Nov 17 '18 at 14:25
marc_s
571k12811031252
asked Nov 17 '18 at 13:22
osotorrio
184314
1
You seem to be under the belief that SQl Server processes anINSERT, or at least within aBEGIN TRY...CATCHrow by row. SQL Server works in datasets and will process the whole dataset in one go. If 1 row out of 1M has an error, then the entire statement errors and is rolled back. When working with a DBMS you need to not think in rows, and think in sets of data. This is why your second attempt didn't work. AnINSERTdoesn't work like aFOR EACHloop (nor does aSELECT,UPDATE, DELETE`, etc).
– Larnu
Nov 17 '18 at 13:32
add a comment |
I have a stored procedure that does an insert like this one:
INSERT INTO [TargetTable] ([Hash], [Year], [Metadata])
SELECT [Hash], [Year], [Metadata]
FROM [SourceTable]
This query will fail because the [TargetTable] has data already and an unique index on the [Hash] column. It cannot insert duplicates.
How I can insert only the non-duplicates? I guess I could select first only the non-duplicates and then do the inserts. But the select would be more complex.
Another approach I tried is to ignore the duplicates. For instance:
BEGIN TRY
INSERT INTO [TargetTable] ([Hash], [Event], [Year], [Metadata])
SELECT [Hash], [Event], [Year], [Metadata]
FROM [SourceTable]
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH
I was expecting something like, try to insert this, if you cannot insert it, then ignore it, and then continue inserting the rest of rows.
sql
sql-server edited Nov 17 '18 at 14:25
marc_s
571k12811031252
asked Nov 17 '18 at 13:22
osotorrio
184314
I have a stored procedure that does an insert like this one:
INSERT INTO [TargetTable] ([Hash], [Year], [Metadata])
SELECT [Hash], [Year], [Metadata]
FROM [SourceTable]
This query will fail because the [TargetTable] has data already and an unique index on the [Hash] column. It cannot insert duplicates.
How I can insert only the non-duplicates? I guess I could select first only the non-duplicates and then do the inserts. But the select would be more complex.
Another approach I tried is to ignore the duplicates. For instance:
BEGIN TRY
INSERT INTO [TargetTable] ([Hash], [Event], [Year], [Metadata])
SELECT [Hash], [Event], [Year], [Metadata]
FROM [SourceTable]
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH
I was expecting something like, try to insert this, if you cannot insert it, then ignore it, and then continue inserting the rest of rows.
sql
sql-server sql
sql-server edited Nov 17 '18 at 14:25
marc_s
571k12811031252
asked Nov 17 '18 at 13:22
osotorrio
184314
edited Nov 17 '18 at 14:25
marc_s
571k12811031252
asked Nov 17 '18 at 13:22
osotorrio
184314
edited Nov 17 '18 at 14:25
marc_s
571k12811031252
edited Nov 17 '18 at 14:25
marc_s
571k12811031252
edited Nov 17 '18 at 14:25
marc_s
571k12811031252
571k12811031252
asked Nov 17 '18 at 13:22
osotorrio
184314
asked Nov 17 '18 at 13:22
osotorrio
184314
asked Nov 17 '18 at 13:22
osotorrio
184314
184314
1
You seem to be under the belief that SQl Server processes anINSERT, or at least within aBEGIN TRY...CATCHrow by row. SQL Server works in datasets and will process the whole dataset in one go. If 1 row out of 1M has an error, then the entire statement errors and is rolled back. When working with a DBMS you need to not think in rows, and think in sets of data. This is why your second attempt didn't work. AnINSERTdoesn't work like aFOR EACHloop (nor does aSELECT,UPDATE, DELETE`, etc).
– Larnu
Nov 17 '18 at 13:32
add a comment |
1
You seem to be under the belief that SQl Server processes anINSERT, or at least within aBEGIN TRY...CATCHrow by row. SQL Server works in datasets and will process the whole dataset in one go. If 1 row out of 1M has an error, then the entire statement errors and is rolled back. When working with a DBMS you need to not think in rows, and think in sets of data. This is why your second attempt didn't work. AnINSERTdoesn't work like aFOR EACHloop (nor does aSELECT,UPDATE, DELETE`, etc).
– Larnu
Nov 17 '18 at 13:32
1
1
You seem to be under the belief that SQl Server processes an
INSERT, or at least within a BEGIN TRY...CATCH row by row. SQL Server works in datasets and will process the whole dataset in one go. If 1 row out of 1M has an error, then the entire statement errors and is rolled back. When working with a DBMS you need to not think in rows, and think in sets of data. This is why your second attempt didn't work. An INSERT doesn't work like a FOR EACH loop (nor does a SELECT, UPDATE, DELETE`, etc).– Larnu
Nov 17 '18 at 13:32
You seem to be under the belief that SQl Server processes an
INSERT, or at least within a BEGIN TRY...CATCH row by row. SQL Server works in datasets and will process the whole dataset in one go. If 1 row out of 1M has an error, then the entire statement errors and is rolled back. When working with a DBMS you need to not think in rows, and think in sets of data. This is why your second attempt didn't work. An INSERT doesn't work like a FOR EACH loop (nor does a SELECT, UPDATE, DELETE`, etc).– Larnu
Nov 17 '18 at 13:32
add a comment |
1 Answer
1
active
oldest
votes
If no other inserts are happening at the same time, then this should work:
INSERT INTO [TargetTable] ([Hash], [Year], [Metadata])
SELECT st.[Hash], st.[Year], st.[Metadata]
FROM (SELECT st.*,
ROW_NUMBER() OVER (PARTITION BY hash ORDER BY (SELECT NULL)) as seqnum
FROM SourceTable st
) st
WHERE seqnum = 1 AND -- ensure duplicates are not in source table
NOT EXISTS (SELECT 1 FROM TargetTable tt WHERE tt.hash = st.hash);
answered Nov 17 '18 at 13:23
Gordon Linoff
759k35293399
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
});
}
});
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%2f53351660%2fsql-server-continue-inserting-rows-after-exception%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
If no other inserts are happening at the same time, then this should work:
INSERT INTO [TargetTable] ([Hash], [Year], [Metadata])
SELECT st.[Hash], st.[Year], st.[Metadata]
FROM (SELECT st.*,
ROW_NUMBER() OVER (PARTITION BY hash ORDER BY (SELECT NULL)) as seqnum
FROM SourceTable st
) st
WHERE seqnum = 1 AND -- ensure duplicates are not in source table
NOT EXISTS (SELECT 1 FROM TargetTable tt WHERE tt.hash = st.hash);
answered Nov 17 '18 at 13:23
Gordon Linoff
759k35293399
add a comment |
If no other inserts are happening at the same time, then this should work:
INSERT INTO [TargetTable] ([Hash], [Year], [Metadata])
SELECT st.[Hash], st.[Year], st.[Metadata]
FROM (SELECT st.*,
ROW_NUMBER() OVER (PARTITION BY hash ORDER BY (SELECT NULL)) as seqnum
FROM SourceTable st
) st
WHERE seqnum = 1 AND -- ensure duplicates are not in source table
NOT EXISTS (SELECT 1 FROM TargetTable tt WHERE tt.hash = st.hash);
answered Nov 17 '18 at 13:23
Gordon Linoff
759k35293399
add a comment |
If no other inserts are happening at the same time, then this should work:
INSERT INTO [TargetTable] ([Hash], [Year], [Metadata])
SELECT st.[Hash], st.[Year], st.[Metadata]
FROM (SELECT st.*,
ROW_NUMBER() OVER (PARTITION BY hash ORDER BY (SELECT NULL)) as seqnum
FROM SourceTable st
) st
WHERE seqnum = 1 AND -- ensure duplicates are not in source table
NOT EXISTS (SELECT 1 FROM TargetTable tt WHERE tt.hash = st.hash);
answered Nov 17 '18 at 13:23
Gordon Linoff
759k35293399
If no other inserts are happening at the same time, then this should work:
INSERT INTO [TargetTable] ([Hash], [Year], [Metadata])
SELECT st.[Hash], st.[Year], st.[Metadata]
FROM (SELECT st.*,
ROW_NUMBER() OVER (PARTITION BY hash ORDER BY (SELECT NULL)) as seqnum
FROM SourceTable st
) st
WHERE seqnum = 1 AND -- ensure duplicates are not in source table
NOT EXISTS (SELECT 1 FROM TargetTable tt WHERE tt.hash = st.hash);
answered Nov 17 '18 at 13:23
Gordon Linoff
759k35293399
answered Nov 17 '18 at 13:23
Gordon Linoff
759k35293399
answered Nov 17 '18 at 13:23
Gordon Linoff
759k35293399
answered Nov 17 '18 at 13:23
Gordon Linoff
759k35293399
759k35293399
add a comment |
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
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%2f53351660%2fsql-server-continue-inserting-rows-after-exception%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
1
You seem to be under the belief that SQl Server processes an
INSERT, or at least within aBEGIN TRY...CATCHrow by row. SQL Server works in datasets and will process the whole dataset in one go. If 1 row out of 1M has an error, then the entire statement errors and is rolled back. When working with a DBMS you need to not think in rows, and think in sets of data. This is why your second attempt didn't work. AnINSERTdoesn't work like aFOR EACHloop (nor does aSELECT,UPDATE, DELETE`, etc).– Larnu
Nov 17 '18 at 13:32