SQL Server : insert into another table values if requirement met in other table
DECLARE @AccountID VARCHAR(10)
SELECT @AccountID = AccountID
FROM MuOnlineSlow.dbo.T_VIPList
WHERE Type = 1
INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE ([AccountID], [name_full], [name], [history], [hex], [cat], [itemid], [level], [luck], [skill], [excellent], [ancient], [time], [has_exe_1], [has_exe_2], [has_exe_4], [has_exe_8], [has_exe_16], [has_exe_32], [socket_exe_6], [socket_exe_7], [socket_exe_8], [server])
VALUES (@AccountID,
'<span style="color:;font-family: tahoma;font-size: 12px;">Bundle of Jewel of Soul </span>',
'Bundle of Jewel of Soul',
'VIP REWARD',
'1F0000000000000000C000FFFFFFFFFF0001D560FFFFFFFFFFFFFFFFFFFFFFFF',
12, 31, 0, 0, 0, 0, 0,
1542466494, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'DEFAULT')
Problem is that query inserts only last information found in T_VIPList table into WEB_WAREHOUSE table, but I have 6 rows with different AccountID. Why doesn't it enter the new information in the WEB_WAREHOUSE with all AccountID?
Any ideas?
sql-server edited Nov 21 '18 at 21:23
marc_s
583k13011241270
asked Nov 21 '18 at 20:58
wiralywiraly
31
add a comment |
DECLARE @AccountID VARCHAR(10)
SELECT @AccountID = AccountID
FROM MuOnlineSlow.dbo.T_VIPList
WHERE Type = 1
INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE ([AccountID], [name_full], [name], [history], [hex], [cat], [itemid], [level], [luck], [skill], [excellent], [ancient], [time], [has_exe_1], [has_exe_2], [has_exe_4], [has_exe_8], [has_exe_16], [has_exe_32], [socket_exe_6], [socket_exe_7], [socket_exe_8], [server])
VALUES (@AccountID,
'<span style="color:;font-family: tahoma;font-size: 12px;">Bundle of Jewel of Soul </span>',
'Bundle of Jewel of Soul',
'VIP REWARD',
'1F0000000000000000C000FFFFFFFFFF0001D560FFFFFFFFFFFFFFFFFFFFFFFF',
12, 31, 0, 0, 0, 0, 0,
1542466494, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'DEFAULT')
Problem is that query inserts only last information found in T_VIPList table into WEB_WAREHOUSE table, but I have 6 rows with different AccountID. Why doesn't it enter the new information in the WEB_WAREHOUSE with all AccountID?
Any ideas?
sql-server edited Nov 21 '18 at 21:23
marc_s
583k13011241270
asked Nov 21 '18 at 20:58
wiralywiraly
31
add a comment |
DECLARE @AccountID VARCHAR(10)
SELECT @AccountID = AccountID
FROM MuOnlineSlow.dbo.T_VIPList
WHERE Type = 1
INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE ([AccountID], [name_full], [name], [history], [hex], [cat], [itemid], [level], [luck], [skill], [excellent], [ancient], [time], [has_exe_1], [has_exe_2], [has_exe_4], [has_exe_8], [has_exe_16], [has_exe_32], [socket_exe_6], [socket_exe_7], [socket_exe_8], [server])
VALUES (@AccountID,
'<span style="color:;font-family: tahoma;font-size: 12px;">Bundle of Jewel of Soul </span>',
'Bundle of Jewel of Soul',
'VIP REWARD',
'1F0000000000000000C000FFFFFFFFFF0001D560FFFFFFFFFFFFFFFFFFFFFFFF',
12, 31, 0, 0, 0, 0, 0,
1542466494, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'DEFAULT')
Problem is that query inserts only last information found in T_VIPList table into WEB_WAREHOUSE table, but I have 6 rows with different AccountID. Why doesn't it enter the new information in the WEB_WAREHOUSE with all AccountID?
Any ideas?
sql-server edited Nov 21 '18 at 21:23
marc_s
583k13011241270
asked Nov 21 '18 at 20:58
wiralywiraly
31
DECLARE @AccountID VARCHAR(10)
SELECT @AccountID = AccountID
FROM MuOnlineSlow.dbo.T_VIPList
WHERE Type = 1
INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE ([AccountID], [name_full], [name], [history], [hex], [cat], [itemid], [level], [luck], [skill], [excellent], [ancient], [time], [has_exe_1], [has_exe_2], [has_exe_4], [has_exe_8], [has_exe_16], [has_exe_32], [socket_exe_6], [socket_exe_7], [socket_exe_8], [server])
VALUES (@AccountID,
'<span style="color:;font-family: tahoma;font-size: 12px;">Bundle of Jewel of Soul </span>',
'Bundle of Jewel of Soul',
'VIP REWARD',
'1F0000000000000000C000FFFFFFFFFF0001D560FFFFFFFFFFFFFFFFFFFFFFFF',
12, 31, 0, 0, 0, 0, 0,
1542466494, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'DEFAULT')
Problem is that query inserts only last information found in T_VIPList table into WEB_WAREHOUSE table, but I have 6 rows with different AccountID. Why doesn't it enter the new information in the WEB_WAREHOUSE with all AccountID?
Any ideas?
sql-server sql-server edited Nov 21 '18 at 21:23
marc_s
583k13011241270
asked Nov 21 '18 at 20:58
wiralywiraly
31
edited Nov 21 '18 at 21:23
marc_s
583k13011241270
asked Nov 21 '18 at 20:58
wiralywiraly
31
edited Nov 21 '18 at 21:23
marc_s
583k13011241270
edited Nov 21 '18 at 21:23
marc_s
583k13011241270
edited Nov 21 '18 at 21:23
marc_s
583k13011241270
583k13011241270
asked Nov 21 '18 at 20:58
wiralywiraly
31
asked Nov 21 '18 at 20:58
wiralywiraly
31
asked Nov 21 '18 at 20:58
wiralywiraly
31
31
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
I think you want something closer to this, which will SELECT all of the AccountIDs from your VIPList with Type 1:
INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE
([AccountID], ..., [server]))
SELECT vip.AccountID, ..., 'DEFAULT'
FROM MuOnlineSlow.dbo.T_VIPList vip
WHERE vip.Type = 1;
And, assuming AccountID is your primary key, you could make the insert safer by checking for existence:
INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE
([AccountID], ..., [server]))
SELECT vip.AccountID, ..., 'DEFAULT'
FROM MuOnlineSlow.dbo.T_VIPList vip
WHERE vip.Type = 1
AND NOT EXISTS(SELECT 1
FROM MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE w
WHERE w.AccountID = vip.AccountID);
answered Nov 21 '18 at 21:03
Joe StefanelliJoe Stefanelli
112k14193209
Well my idea is to insert manually typed Values with replaced AccountID from T_VIPList table. Everything works, but like i said, it inserts only last rows AccountID.
– wiraly
Nov 21 '18 at 21:16
@wiraly The fundamental problem with your approach is that@AccountIDis a variable that can only hold a single value. You need to use aSELECTstatement to get all of the different AccountIDs in a singleINSERT.
– Joe Stefanelli
Nov 21 '18 at 21:19
1
Good Job. Thank you for your answer. This worker like i wanted - INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE ([AccountID], ..., [server])) SELECT vip.AccountID, ..., 'DEFAULT' FROM MuOnlineSlow.dbo.T_VIPList vip WHERE vip.Type = 1;
– wiraly
Nov 21 '18 at 21:35
add a comment |
@AccountID is a variable that can only hold one value, not a list/array/vector. So if you wonder why your SELECT doesn't throw an error when the FROM with WHERE returns 6 rows and therefore 6 values are to be assigned to a variable: because this is one of the nondeterministic behaviours of SQL. A random value "wins" and will be assigned from the 6 returned. So you need to combine INSERT with SELECT without variables to get all rows (= a table) as other answer(s) show(s).
answered Nov 21 '18 at 21:20
Dávid LaczkóDávid Laczkó
429129
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%2f53420390%2fsql-server-insert-into-another-table-values-if-requirement-met-in-other-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I think you want something closer to this, which will SELECT all of the AccountIDs from your VIPList with Type 1:
INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE
([AccountID], ..., [server]))
SELECT vip.AccountID, ..., 'DEFAULT'
FROM MuOnlineSlow.dbo.T_VIPList vip
WHERE vip.Type = 1;
And, assuming AccountID is your primary key, you could make the insert safer by checking for existence:
INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE
([AccountID], ..., [server]))
SELECT vip.AccountID, ..., 'DEFAULT'
FROM MuOnlineSlow.dbo.T_VIPList vip
WHERE vip.Type = 1
AND NOT EXISTS(SELECT 1
FROM MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE w
WHERE w.AccountID = vip.AccountID);
answered Nov 21 '18 at 21:03
Joe StefanelliJoe Stefanelli
112k14193209
Well my idea is to insert manually typed Values with replaced AccountID from T_VIPList table. Everything works, but like i said, it inserts only last rows AccountID.
– wiraly
Nov 21 '18 at 21:16
@wiraly The fundamental problem with your approach is that@AccountIDis a variable that can only hold a single value. You need to use aSELECTstatement to get all of the different AccountIDs in a singleINSERT.
– Joe Stefanelli
Nov 21 '18 at 21:19
1
Good Job. Thank you for your answer. This worker like i wanted - INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE ([AccountID], ..., [server])) SELECT vip.AccountID, ..., 'DEFAULT' FROM MuOnlineSlow.dbo.T_VIPList vip WHERE vip.Type = 1;
– wiraly
Nov 21 '18 at 21:35
add a comment |
I think you want something closer to this, which will SELECT all of the AccountIDs from your VIPList with Type 1:
INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE
([AccountID], ..., [server]))
SELECT vip.AccountID, ..., 'DEFAULT'
FROM MuOnlineSlow.dbo.T_VIPList vip
WHERE vip.Type = 1;
And, assuming AccountID is your primary key, you could make the insert safer by checking for existence:
INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE
([AccountID], ..., [server]))
SELECT vip.AccountID, ..., 'DEFAULT'
FROM MuOnlineSlow.dbo.T_VIPList vip
WHERE vip.Type = 1
AND NOT EXISTS(SELECT 1
FROM MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE w
WHERE w.AccountID = vip.AccountID);
answered Nov 21 '18 at 21:03
Joe StefanelliJoe Stefanelli
112k14193209
Well my idea is to insert manually typed Values with replaced AccountID from T_VIPList table. Everything works, but like i said, it inserts only last rows AccountID.
– wiraly
Nov 21 '18 at 21:16
@wiraly The fundamental problem with your approach is that@AccountIDis a variable that can only hold a single value. You need to use aSELECTstatement to get all of the different AccountIDs in a singleINSERT.
– Joe Stefanelli
Nov 21 '18 at 21:19
1
Good Job. Thank you for your answer. This worker like i wanted - INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE ([AccountID], ..., [server])) SELECT vip.AccountID, ..., 'DEFAULT' FROM MuOnlineSlow.dbo.T_VIPList vip WHERE vip.Type = 1;
– wiraly
Nov 21 '18 at 21:35
add a comment |
I think you want something closer to this, which will SELECT all of the AccountIDs from your VIPList with Type 1:
INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE
([AccountID], ..., [server]))
SELECT vip.AccountID, ..., 'DEFAULT'
FROM MuOnlineSlow.dbo.T_VIPList vip
WHERE vip.Type = 1;
And, assuming AccountID is your primary key, you could make the insert safer by checking for existence:
INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE
([AccountID], ..., [server]))
SELECT vip.AccountID, ..., 'DEFAULT'
FROM MuOnlineSlow.dbo.T_VIPList vip
WHERE vip.Type = 1
AND NOT EXISTS(SELECT 1
FROM MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE w
WHERE w.AccountID = vip.AccountID);
answered Nov 21 '18 at 21:03
Joe StefanelliJoe Stefanelli
112k14193209
I think you want something closer to this, which will SELECT all of the AccountIDs from your VIPList with Type 1:
INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE
([AccountID], ..., [server]))
SELECT vip.AccountID, ..., 'DEFAULT'
FROM MuOnlineSlow.dbo.T_VIPList vip
WHERE vip.Type = 1;
And, assuming AccountID is your primary key, you could make the insert safer by checking for existence:
INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE
([AccountID], ..., [server]))
SELECT vip.AccountID, ..., 'DEFAULT'
FROM MuOnlineSlow.dbo.T_VIPList vip
WHERE vip.Type = 1
AND NOT EXISTS(SELECT 1
FROM MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE w
WHERE w.AccountID = vip.AccountID);
answered Nov 21 '18 at 21:03
Joe StefanelliJoe Stefanelli
112k14193209
edited Nov 21 '18 at 21:10
answered Nov 21 '18 at 21:03
Joe StefanelliJoe Stefanelli
112k14193209
answered Nov 21 '18 at 21:03
Joe StefanelliJoe Stefanelli
112k14193209
answered Nov 21 '18 at 21:03
Joe StefanelliJoe Stefanelli
112k14193209
112k14193209
Well my idea is to insert manually typed Values with replaced AccountID from T_VIPList table. Everything works, but like i said, it inserts only last rows AccountID.
– wiraly
Nov 21 '18 at 21:16
@wiraly The fundamental problem with your approach is that@AccountIDis a variable that can only hold a single value. You need to use aSELECTstatement to get all of the different AccountIDs in a singleINSERT.
– Joe Stefanelli
Nov 21 '18 at 21:19
1
Good Job. Thank you for your answer. This worker like i wanted - INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE ([AccountID], ..., [server])) SELECT vip.AccountID, ..., 'DEFAULT' FROM MuOnlineSlow.dbo.T_VIPList vip WHERE vip.Type = 1;
– wiraly
Nov 21 '18 at 21:35
add a comment |
Well my idea is to insert manually typed Values with replaced AccountID from T_VIPList table. Everything works, but like i said, it inserts only last rows AccountID.
– wiraly
Nov 21 '18 at 21:16
@wiraly The fundamental problem with your approach is that@AccountIDis a variable that can only hold a single value. You need to use aSELECTstatement to get all of the different AccountIDs in a singleINSERT.
– Joe Stefanelli
Nov 21 '18 at 21:19
1
Good Job. Thank you for your answer. This worker like i wanted - INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE ([AccountID], ..., [server])) SELECT vip.AccountID, ..., 'DEFAULT' FROM MuOnlineSlow.dbo.T_VIPList vip WHERE vip.Type = 1;
– wiraly
Nov 21 '18 at 21:35
Well my idea is to insert manually typed Values with replaced AccountID from T_VIPList table. Everything works, but like i said, it inserts only last rows AccountID.
– wiraly
Nov 21 '18 at 21:16
Well my idea is to insert manually typed Values with replaced AccountID from T_VIPList table. Everything works, but like i said, it inserts only last rows AccountID.
– wiraly
Nov 21 '18 at 21:16
@wiraly The fundamental problem with your approach is that
@AccountID is a variable that can only hold a single value. You need to use a SELECT statement to get all of the different AccountIDs in a single INSERT.– Joe Stefanelli
Nov 21 '18 at 21:19
@wiraly The fundamental problem with your approach is that
@AccountID is a variable that can only hold a single value. You need to use a SELECT statement to get all of the different AccountIDs in a single INSERT.– Joe Stefanelli
Nov 21 '18 at 21:19
1
1
Good Job. Thank you for your answer. This worker like i wanted - INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE ([AccountID], ..., [server])) SELECT vip.AccountID, ..., 'DEFAULT' FROM MuOnlineSlow.dbo.T_VIPList vip WHERE vip.Type = 1;
– wiraly
Nov 21 '18 at 21:35
Good Job. Thank you for your answer. This worker like i wanted - INSERT INTO MUONLINEWEBS.dbo.MUONLINEWEBS_WEB_WAREHOUSE ([AccountID], ..., [server])) SELECT vip.AccountID, ..., 'DEFAULT' FROM MuOnlineSlow.dbo.T_VIPList vip WHERE vip.Type = 1;
– wiraly
Nov 21 '18 at 21:35
add a comment |
@AccountID is a variable that can only hold one value, not a list/array/vector. So if you wonder why your SELECT doesn't throw an error when the FROM with WHERE returns 6 rows and therefore 6 values are to be assigned to a variable: because this is one of the nondeterministic behaviours of SQL. A random value "wins" and will be assigned from the 6 returned. So you need to combine INSERT with SELECT without variables to get all rows (= a table) as other answer(s) show(s).
answered Nov 21 '18 at 21:20
Dávid LaczkóDávid Laczkó
429129
add a comment |
@AccountID is a variable that can only hold one value, not a list/array/vector. So if you wonder why your SELECT doesn't throw an error when the FROM with WHERE returns 6 rows and therefore 6 values are to be assigned to a variable: because this is one of the nondeterministic behaviours of SQL. A random value "wins" and will be assigned from the 6 returned. So you need to combine INSERT with SELECT without variables to get all rows (= a table) as other answer(s) show(s).
answered Nov 21 '18 at 21:20
Dávid LaczkóDávid Laczkó
429129
add a comment |
@AccountID is a variable that can only hold one value, not a list/array/vector. So if you wonder why your SELECT doesn't throw an error when the FROM with WHERE returns 6 rows and therefore 6 values are to be assigned to a variable: because this is one of the nondeterministic behaviours of SQL. A random value "wins" and will be assigned from the 6 returned. So you need to combine INSERT with SELECT without variables to get all rows (= a table) as other answer(s) show(s).
answered Nov 21 '18 at 21:20
Dávid LaczkóDávid Laczkó
429129
@AccountID is a variable that can only hold one value, not a list/array/vector. So if you wonder why your SELECT doesn't throw an error when the FROM with WHERE returns 6 rows and therefore 6 values are to be assigned to a variable: because this is one of the nondeterministic behaviours of SQL. A random value "wins" and will be assigned from the 6 returned. So you need to combine INSERT with SELECT without variables to get all rows (= a table) as other answer(s) show(s).
answered Nov 21 '18 at 21:20
Dávid LaczkóDávid Laczkó
429129
answered Nov 21 '18 at 21:20
Dávid LaczkóDávid Laczkó
429129
answered Nov 21 '18 at 21:20
Dávid LaczkóDávid Laczkó
429129
answered Nov 21 '18 at 21:20
Dávid LaczkóDávid Laczkó
429129
429129
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.
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%2f53420390%2fsql-server-insert-into-another-table-values-if-requirement-met-in-other-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
