DELETE else UPDATE from temp table
This may seem easy but I have been looking for some hours now.
How do I insert rows in a target table that do not exist in the temp table,
and at the same time delete rows in the target table that do exist in the temp table? It has to be transaction secure, in teradata, and if possible performant.
MERGE does not support delete and insert at the same time apparently.
merge teradata
asked Nov 23 '18 at 17:03
Rusty75Rusty75
115112
add a comment |
This may seem easy but I have been looking for some hours now.
How do I insert rows in a target table that do not exist in the temp table,
and at the same time delete rows in the target table that do exist in the temp table? It has to be transaction secure, in teradata, and if possible performant.
MERGE does not support delete and insert at the same time apparently.
merge teradata
asked Nov 23 '18 at 17:03
Rusty75Rusty75
115112
add a comment |
This may seem easy but I have been looking for some hours now.
How do I insert rows in a target table that do not exist in the temp table,
and at the same time delete rows in the target table that do exist in the temp table? It has to be transaction secure, in teradata, and if possible performant.
MERGE does not support delete and insert at the same time apparently.
merge teradata
asked Nov 23 '18 at 17:03
Rusty75Rusty75
115112
This may seem easy but I have been looking for some hours now.
How do I insert rows in a target table that do not exist in the temp table,
and at the same time delete rows in the target table that do exist in the temp table? It has to be transaction secure, in teradata, and if possible performant.
MERGE does not support delete and insert at the same time apparently.
merge teradata
merge teradata
asked Nov 23 '18 at 17:03
Rusty75Rusty75
115112
asked Nov 23 '18 at 17:03
Rusty75Rusty75
115112
asked Nov 23 '18 at 17:03
Rusty75Rusty75
115112
asked Nov 23 '18 at 17:03
Rusty75Rusty75
115112
asked Nov 23 '18 at 17:03
Rusty75Rusty75
115112
115112
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Along with temporary/work table, you can make use of BTET to make delete and insert transaction safe.
Sample example shown below
CREATE TABLE temp_Table_2 AS (SELECT * FROM target_Table WHERE EXISTS (SELECT 1 FROM temp_Table WHERE target_Table.key_cols = temp_Table.key_cols)) WITH DATA;
BT;
--First delete rows which are present in temp table
DELETE FROM target_Table WHERE EXISTS (SELECT 1 FROM temp_Table WHERE target_Table.key_cols = temp_Table.key_cols);
--Second Insert rows which were not exist in target table
INSERT INTO target_Table
col_1,
col_1,
.
.
col_n
)
SELECT
col_1,
col_1,
.
.
col_n
FROM temp_Table
WHERE NOT EXISTS (SELECT 1 FROM temp_Table_2 WHERE temp_Table_2.key_cols = temp_Table.key_cols);
ET;
DROP TABLE temp_Table_2;
Note: You can tweak the example if there is any third table from where you want to INSERT rows into target table that do not exists in Temp table
answered Nov 25 '18 at 9:01
Pradeep KhatriPradeep Khatri
764
1
The INSERT should be coded as MERGE because this might be more efficient (avoiding spool). And instead of BT/ET a MultiStatement can be used.
– dnoeth
Nov 25 '18 at 10:35
Thanks Dnoeth, MultiStatement seems better idea than BT/ET.
– Pradeep Khatri
Nov 25 '18 at 14:57
But a multistatement is not transaction secure, or?
– Rusty75
Nov 25 '18 at 16:30
And also, is a MERGE more efficient than a simple INSERT...SELECT? How is that possible?
– Rusty75
Nov 25 '18 at 16:33
oh and finally you would also be inserting the rows that you wanted to just delete ;)
– Rusty75
Nov 25 '18 at 16:40
|
show 1 more 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%2f53450562%2fdelete-else-update-from-temp-table%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
Along with temporary/work table, you can make use of BTET to make delete and insert transaction safe.
Sample example shown below
CREATE TABLE temp_Table_2 AS (SELECT * FROM target_Table WHERE EXISTS (SELECT 1 FROM temp_Table WHERE target_Table.key_cols = temp_Table.key_cols)) WITH DATA;
BT;
--First delete rows which are present in temp table
DELETE FROM target_Table WHERE EXISTS (SELECT 1 FROM temp_Table WHERE target_Table.key_cols = temp_Table.key_cols);
--Second Insert rows which were not exist in target table
INSERT INTO target_Table
col_1,
col_1,
.
.
col_n
)
SELECT
col_1,
col_1,
.
.
col_n
FROM temp_Table
WHERE NOT EXISTS (SELECT 1 FROM temp_Table_2 WHERE temp_Table_2.key_cols = temp_Table.key_cols);
ET;
DROP TABLE temp_Table_2;
Note: You can tweak the example if there is any third table from where you want to INSERT rows into target table that do not exists in Temp table
answered Nov 25 '18 at 9:01
Pradeep KhatriPradeep Khatri
764
1
The INSERT should be coded as MERGE because this might be more efficient (avoiding spool). And instead of BT/ET a MultiStatement can be used.
– dnoeth
Nov 25 '18 at 10:35
Thanks Dnoeth, MultiStatement seems better idea than BT/ET.
– Pradeep Khatri
Nov 25 '18 at 14:57
But a multistatement is not transaction secure, or?
– Rusty75
Nov 25 '18 at 16:30
And also, is a MERGE more efficient than a simple INSERT...SELECT? How is that possible?
– Rusty75
Nov 25 '18 at 16:33
oh and finally you would also be inserting the rows that you wanted to just delete ;)
– Rusty75
Nov 25 '18 at 16:40
|
show 1 more comment
Along with temporary/work table, you can make use of BTET to make delete and insert transaction safe.
Sample example shown below
CREATE TABLE temp_Table_2 AS (SELECT * FROM target_Table WHERE EXISTS (SELECT 1 FROM temp_Table WHERE target_Table.key_cols = temp_Table.key_cols)) WITH DATA;
BT;
--First delete rows which are present in temp table
DELETE FROM target_Table WHERE EXISTS (SELECT 1 FROM temp_Table WHERE target_Table.key_cols = temp_Table.key_cols);
--Second Insert rows which were not exist in target table
INSERT INTO target_Table
col_1,
col_1,
.
.
col_n
)
SELECT
col_1,
col_1,
.
.
col_n
FROM temp_Table
WHERE NOT EXISTS (SELECT 1 FROM temp_Table_2 WHERE temp_Table_2.key_cols = temp_Table.key_cols);
ET;
DROP TABLE temp_Table_2;
Note: You can tweak the example if there is any third table from where you want to INSERT rows into target table that do not exists in Temp table
answered Nov 25 '18 at 9:01
Pradeep KhatriPradeep Khatri
764
1
The INSERT should be coded as MERGE because this might be more efficient (avoiding spool). And instead of BT/ET a MultiStatement can be used.
– dnoeth
Nov 25 '18 at 10:35
Thanks Dnoeth, MultiStatement seems better idea than BT/ET.
– Pradeep Khatri
Nov 25 '18 at 14:57
But a multistatement is not transaction secure, or?
– Rusty75
Nov 25 '18 at 16:30
And also, is a MERGE more efficient than a simple INSERT...SELECT? How is that possible?
– Rusty75
Nov 25 '18 at 16:33
oh and finally you would also be inserting the rows that you wanted to just delete ;)
– Rusty75
Nov 25 '18 at 16:40
|
show 1 more comment
Along with temporary/work table, you can make use of BTET to make delete and insert transaction safe.
Sample example shown below
CREATE TABLE temp_Table_2 AS (SELECT * FROM target_Table WHERE EXISTS (SELECT 1 FROM temp_Table WHERE target_Table.key_cols = temp_Table.key_cols)) WITH DATA;
BT;
--First delete rows which are present in temp table
DELETE FROM target_Table WHERE EXISTS (SELECT 1 FROM temp_Table WHERE target_Table.key_cols = temp_Table.key_cols);
--Second Insert rows which were not exist in target table
INSERT INTO target_Table
col_1,
col_1,
.
.
col_n
)
SELECT
col_1,
col_1,
.
.
col_n
FROM temp_Table
WHERE NOT EXISTS (SELECT 1 FROM temp_Table_2 WHERE temp_Table_2.key_cols = temp_Table.key_cols);
ET;
DROP TABLE temp_Table_2;
Note: You can tweak the example if there is any third table from where you want to INSERT rows into target table that do not exists in Temp table
answered Nov 25 '18 at 9:01
Pradeep KhatriPradeep Khatri
764
Along with temporary/work table, you can make use of BTET to make delete and insert transaction safe.
Sample example shown below
CREATE TABLE temp_Table_2 AS (SELECT * FROM target_Table WHERE EXISTS (SELECT 1 FROM temp_Table WHERE target_Table.key_cols = temp_Table.key_cols)) WITH DATA;
BT;
--First delete rows which are present in temp table
DELETE FROM target_Table WHERE EXISTS (SELECT 1 FROM temp_Table WHERE target_Table.key_cols = temp_Table.key_cols);
--Second Insert rows which were not exist in target table
INSERT INTO target_Table
col_1,
col_1,
.
.
col_n
)
SELECT
col_1,
col_1,
.
.
col_n
FROM temp_Table
WHERE NOT EXISTS (SELECT 1 FROM temp_Table_2 WHERE temp_Table_2.key_cols = temp_Table.key_cols);
ET;
DROP TABLE temp_Table_2;
Note: You can tweak the example if there is any third table from where you want to INSERT rows into target table that do not exists in Temp table
answered Nov 25 '18 at 9:01
Pradeep KhatriPradeep Khatri
764
edited Nov 25 '18 at 15:02
answered Nov 25 '18 at 9:01
Pradeep KhatriPradeep Khatri
764
answered Nov 25 '18 at 9:01
Pradeep KhatriPradeep Khatri
764
answered Nov 25 '18 at 9:01
Pradeep KhatriPradeep Khatri
764
764
1
The INSERT should be coded as MERGE because this might be more efficient (avoiding spool). And instead of BT/ET a MultiStatement can be used.
– dnoeth
Nov 25 '18 at 10:35
Thanks Dnoeth, MultiStatement seems better idea than BT/ET.
– Pradeep Khatri
Nov 25 '18 at 14:57
But a multistatement is not transaction secure, or?
– Rusty75
Nov 25 '18 at 16:30
And also, is a MERGE more efficient than a simple INSERT...SELECT? How is that possible?
– Rusty75
Nov 25 '18 at 16:33
oh and finally you would also be inserting the rows that you wanted to just delete ;)
– Rusty75
Nov 25 '18 at 16:40
|
show 1 more comment
1
The INSERT should be coded as MERGE because this might be more efficient (avoiding spool). And instead of BT/ET a MultiStatement can be used.
– dnoeth
Nov 25 '18 at 10:35
Thanks Dnoeth, MultiStatement seems better idea than BT/ET.
– Pradeep Khatri
Nov 25 '18 at 14:57
But a multistatement is not transaction secure, or?
– Rusty75
Nov 25 '18 at 16:30
And also, is a MERGE more efficient than a simple INSERT...SELECT? How is that possible?
– Rusty75
Nov 25 '18 at 16:33
oh and finally you would also be inserting the rows that you wanted to just delete ;)
– Rusty75
Nov 25 '18 at 16:40
1
1
The INSERT should be coded as MERGE because this might be more efficient (avoiding spool). And instead of BT/ET a MultiStatement can be used.
– dnoeth
Nov 25 '18 at 10:35
The INSERT should be coded as MERGE because this might be more efficient (avoiding spool). And instead of BT/ET a MultiStatement can be used.
– dnoeth
Nov 25 '18 at 10:35
Thanks Dnoeth, MultiStatement seems better idea than BT/ET.
– Pradeep Khatri
Nov 25 '18 at 14:57
Thanks Dnoeth, MultiStatement seems better idea than BT/ET.
– Pradeep Khatri
Nov 25 '18 at 14:57
But a multistatement is not transaction secure, or?
– Rusty75
Nov 25 '18 at 16:30
But a multistatement is not transaction secure, or?
– Rusty75
Nov 25 '18 at 16:30
And also, is a MERGE more efficient than a simple INSERT...SELECT? How is that possible?
– Rusty75
Nov 25 '18 at 16:33
And also, is a MERGE more efficient than a simple INSERT...SELECT? How is that possible?
– Rusty75
Nov 25 '18 at 16:33
oh and finally you would also be inserting the rows that you wanted to just delete ;)
– Rusty75
Nov 25 '18 at 16:40
oh and finally you would also be inserting the rows that you wanted to just delete ;)
– Rusty75
Nov 25 '18 at 16:40
|
show 1 more 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.
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%2f53450562%2fdelete-else-update-from-temp-table%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
