Include hard-coded value in INSERT..SELECT
1
I have this store procedure in Sql which works fine. But I want to insert a text 'POSTED' into Assesable column while executing the procedure; Need help to achieve this.
CREATE PROCEDURE [dbo].[spBankToAll]
AS
BEGIN
INSERT INTO AllPayandRec
(BID
,EntryDate
,Typez
,FullName
,Assesable)
SELECT Bank.BID
,Bank.EntryDate
,Bank.Type
,Bank.Assesable
FROM Bank
Left JOIN AllPayandRec ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS Null
END
thus Insert 'POSTED' into all rows as part of executing the procedure
sql
sql-server tsql edited Dec 28 '18 at 14:48
Johnny Bones
6,57552677
asked Dec 28 '18 at 14:31
eka24eka24
107
add a comment |
1
I have this store procedure in Sql which works fine. But I want to insert a text 'POSTED' into Assesable column while executing the procedure; Need help to achieve this.
CREATE PROCEDURE [dbo].[spBankToAll]
AS
BEGIN
INSERT INTO AllPayandRec
(BID
,EntryDate
,Typez
,FullName
,Assesable)
SELECT Bank.BID
,Bank.EntryDate
,Bank.Type
,Bank.Assesable
FROM Bank
Left JOIN AllPayandRec ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS Null
END
thus Insert 'POSTED' into all rows as part of executing the procedure
sql
sql-server tsql edited Dec 28 '18 at 14:48
Johnny Bones
6,57552677
asked Dec 28 '18 at 14:31
eka24eka24
107
Did you miss part of the query? This shouldn't work because yourSELECTcolumns don't match up to yourINSERTcolumns (ie you are inserting 5 cols but only selecting 4). Other than that, just replaceBank.Assesablewith'POSTED'.
– Shawn
Dec 28 '18 at 17:25
add a comment |
1
1
1
I have this store procedure in Sql which works fine. But I want to insert a text 'POSTED' into Assesable column while executing the procedure; Need help to achieve this.
CREATE PROCEDURE [dbo].[spBankToAll]
AS
BEGIN
INSERT INTO AllPayandRec
(BID
,EntryDate
,Typez
,FullName
,Assesable)
SELECT Bank.BID
,Bank.EntryDate
,Bank.Type
,Bank.Assesable
FROM Bank
Left JOIN AllPayandRec ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS Null
END
thus Insert 'POSTED' into all rows as part of executing the procedure
sql
sql-server tsql edited Dec 28 '18 at 14:48
Johnny Bones
6,57552677
asked Dec 28 '18 at 14:31
eka24eka24
107
I have this store procedure in Sql which works fine. But I want to insert a text 'POSTED' into Assesable column while executing the procedure; Need help to achieve this.
CREATE PROCEDURE [dbo].[spBankToAll]
AS
BEGIN
INSERT INTO AllPayandRec
(BID
,EntryDate
,Typez
,FullName
,Assesable)
SELECT Bank.BID
,Bank.EntryDate
,Bank.Type
,Bank.Assesable
FROM Bank
Left JOIN AllPayandRec ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS Null
END
thus Insert 'POSTED' into all rows as part of executing the procedure
sql
sql-server tsql sql
sql-server tsql edited Dec 28 '18 at 14:48
Johnny Bones
6,57552677
asked Dec 28 '18 at 14:31
eka24eka24
107
edited Dec 28 '18 at 14:48
Johnny Bones
6,57552677
asked Dec 28 '18 at 14:31
eka24eka24
107
edited Dec 28 '18 at 14:48
Johnny Bones
6,57552677
edited Dec 28 '18 at 14:48
Johnny Bones
6,57552677
edited Dec 28 '18 at 14:48
Johnny Bones
6,57552677
6,57552677
asked Dec 28 '18 at 14:31
eka24eka24
107
asked Dec 28 '18 at 14:31
eka24eka24
107
asked Dec 28 '18 at 14:31
eka24eka24
107
107
Did you miss part of the query? This shouldn't work because yourSELECTcolumns don't match up to yourINSERTcolumns (ie you are inserting 5 cols but only selecting 4). Other than that, just replaceBank.Assesablewith'POSTED'.
– Shawn
Dec 28 '18 at 17:25
add a comment |
Did you miss part of the query? This shouldn't work because yourSELECTcolumns don't match up to yourINSERTcolumns (ie you are inserting 5 cols but only selecting 4). Other than that, just replaceBank.Assesablewith'POSTED'.
– Shawn
Dec 28 '18 at 17:25
Did you miss part of the query? This shouldn't work because your
SELECT columns don't match up to your INSERT columns (ie you are inserting 5 cols but only selecting 4). Other than that, just replace Bank.Assesable with 'POSTED'.– Shawn
Dec 28 '18 at 17:25
Did you miss part of the query? This shouldn't work because your
SELECT columns don't match up to your INSERT columns (ie you are inserting 5 cols but only selecting 4). Other than that, just replace Bank.Assesable with 'POSTED'.– Shawn
Dec 28 '18 at 17:25
add a comment |
3 Answers
3
active
oldest
votes
0
INSERT INTO AllPayandRec (BID, EntryDate, Typez, FullName, Assesable)
SELECT Bank.BID,
Bank.EntryDate,
Bank.Type,
Bank.FullName,
'POSTED'
FROM Bank LEFT JOIN AllPayandRec ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS NULL;
edited Dec 28 '18 at 14:49
Johnny Bones
6,57552677
answered Dec 28 '18 at 14:46
Mahesh KularathneMahesh Kularathne
262
Thanks very much. sorry for the late reply. it worked
– eka24
Dec 31 '18 at 14:32
add a comment |
2
This works perfectly, I think I got grudge downvoted. :o)
CREATE PROCEDURE [dbo].[spBankToAll]
AS
BEGIN
INSERT INTO AllPayandRec
(BID
,EntryDate
,Typez
,FullName
,Assesable)
SELECT Bank.BID
,Bank.EntryDate
,Bank.Type
,Bank.FullName
,'POSTED'
FROM Bank
Left JOIN AllPayandRec ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS Null
END
answered Dec 28 '18 at 14:34
Johnny BonesJohnny Bones
6,57552677
1
I did not downvote, but code without any kind of explanation is only half of an answer.
– Peter B
Dec 28 '18 at 15:02
@PeterB Agreed, but to be fair, this change didn't really require a lot of explanation.
– Shawn
Dec 28 '18 at 17:30
add a comment |
2
Just replace it with "POSTED"in SELECT statement :
INSERT INTO AllPayandRec (BID, EntryDate, Typez, FullName, Assesable)
SELECT Bank.BID, Bank.EntryDate, Bank.Type, Bank.FullName, 'POSTED'
FROM Bank LEFT JOIN
AllPayandRec
ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS NULL;
edited Dec 28 '18 at 14:45
Johnny Bones
6,57552677
answered Dec 28 '18 at 14:34
Yogesh SharmaYogesh Sharma
28.8k51336
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%2f53960111%2finclude-hard-coded-value-in-insert-select%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
INSERT INTO AllPayandRec (BID, EntryDate, Typez, FullName, Assesable)
SELECT Bank.BID,
Bank.EntryDate,
Bank.Type,
Bank.FullName,
'POSTED'
FROM Bank LEFT JOIN AllPayandRec ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS NULL;
edited Dec 28 '18 at 14:49
Johnny Bones
6,57552677
answered Dec 28 '18 at 14:46
Mahesh KularathneMahesh Kularathne
262
Thanks very much. sorry for the late reply. it worked
– eka24
Dec 31 '18 at 14:32
add a comment |
0
INSERT INTO AllPayandRec (BID, EntryDate, Typez, FullName, Assesable)
SELECT Bank.BID,
Bank.EntryDate,
Bank.Type,
Bank.FullName,
'POSTED'
FROM Bank LEFT JOIN AllPayandRec ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS NULL;
edited Dec 28 '18 at 14:49
Johnny Bones
6,57552677
answered Dec 28 '18 at 14:46
Mahesh KularathneMahesh Kularathne
262
Thanks very much. sorry for the late reply. it worked
– eka24
Dec 31 '18 at 14:32
add a comment |
0
0
0
INSERT INTO AllPayandRec (BID, EntryDate, Typez, FullName, Assesable)
SELECT Bank.BID,
Bank.EntryDate,
Bank.Type,
Bank.FullName,
'POSTED'
FROM Bank LEFT JOIN AllPayandRec ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS NULL;
edited Dec 28 '18 at 14:49
Johnny Bones
6,57552677
answered Dec 28 '18 at 14:46
Mahesh KularathneMahesh Kularathne
262
INSERT INTO AllPayandRec (BID, EntryDate, Typez, FullName, Assesable)
SELECT Bank.BID,
Bank.EntryDate,
Bank.Type,
Bank.FullName,
'POSTED'
FROM Bank LEFT JOIN AllPayandRec ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS NULL;
edited Dec 28 '18 at 14:49
Johnny Bones
6,57552677
answered Dec 28 '18 at 14:46
Mahesh KularathneMahesh Kularathne
262
edited Dec 28 '18 at 14:49
Johnny Bones
6,57552677
edited Dec 28 '18 at 14:49
Johnny Bones
6,57552677
edited Dec 28 '18 at 14:49
Johnny Bones
6,57552677
6,57552677
answered Dec 28 '18 at 14:46
Mahesh KularathneMahesh Kularathne
262
answered Dec 28 '18 at 14:46
Mahesh KularathneMahesh Kularathne
262
answered Dec 28 '18 at 14:46
Mahesh KularathneMahesh Kularathne
262
262
Thanks very much. sorry for the late reply. it worked
– eka24
Dec 31 '18 at 14:32
add a comment |
Thanks very much. sorry for the late reply. it worked
– eka24
Dec 31 '18 at 14:32
Thanks very much. sorry for the late reply. it worked
– eka24
Dec 31 '18 at 14:32
Thanks very much. sorry for the late reply. it worked
– eka24
Dec 31 '18 at 14:32
add a comment |
2
This works perfectly, I think I got grudge downvoted. :o)
CREATE PROCEDURE [dbo].[spBankToAll]
AS
BEGIN
INSERT INTO AllPayandRec
(BID
,EntryDate
,Typez
,FullName
,Assesable)
SELECT Bank.BID
,Bank.EntryDate
,Bank.Type
,Bank.FullName
,'POSTED'
FROM Bank
Left JOIN AllPayandRec ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS Null
END
answered Dec 28 '18 at 14:34
Johnny BonesJohnny Bones
6,57552677
1
I did not downvote, but code without any kind of explanation is only half of an answer.
– Peter B
Dec 28 '18 at 15:02
@PeterB Agreed, but to be fair, this change didn't really require a lot of explanation.
– Shawn
Dec 28 '18 at 17:30
add a comment |
2
This works perfectly, I think I got grudge downvoted. :o)
CREATE PROCEDURE [dbo].[spBankToAll]
AS
BEGIN
INSERT INTO AllPayandRec
(BID
,EntryDate
,Typez
,FullName
,Assesable)
SELECT Bank.BID
,Bank.EntryDate
,Bank.Type
,Bank.FullName
,'POSTED'
FROM Bank
Left JOIN AllPayandRec ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS Null
END
answered Dec 28 '18 at 14:34
Johnny BonesJohnny Bones
6,57552677
1
I did not downvote, but code without any kind of explanation is only half of an answer.
– Peter B
Dec 28 '18 at 15:02
@PeterB Agreed, but to be fair, this change didn't really require a lot of explanation.
– Shawn
Dec 28 '18 at 17:30
add a comment |
2
2
2
This works perfectly, I think I got grudge downvoted. :o)
CREATE PROCEDURE [dbo].[spBankToAll]
AS
BEGIN
INSERT INTO AllPayandRec
(BID
,EntryDate
,Typez
,FullName
,Assesable)
SELECT Bank.BID
,Bank.EntryDate
,Bank.Type
,Bank.FullName
,'POSTED'
FROM Bank
Left JOIN AllPayandRec ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS Null
END
answered Dec 28 '18 at 14:34
Johnny BonesJohnny Bones
6,57552677
This works perfectly, I think I got grudge downvoted. :o)
CREATE PROCEDURE [dbo].[spBankToAll]
AS
BEGIN
INSERT INTO AllPayandRec
(BID
,EntryDate
,Typez
,FullName
,Assesable)
SELECT Bank.BID
,Bank.EntryDate
,Bank.Type
,Bank.FullName
,'POSTED'
FROM Bank
Left JOIN AllPayandRec ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS Null
END
answered Dec 28 '18 at 14:34
Johnny BonesJohnny Bones
6,57552677
edited Dec 28 '18 at 14:45
answered Dec 28 '18 at 14:34
Johnny BonesJohnny Bones
6,57552677
answered Dec 28 '18 at 14:34
Johnny BonesJohnny Bones
6,57552677
answered Dec 28 '18 at 14:34
Johnny BonesJohnny Bones
6,57552677
6,57552677
1
I did not downvote, but code without any kind of explanation is only half of an answer.
– Peter B
Dec 28 '18 at 15:02
@PeterB Agreed, but to be fair, this change didn't really require a lot of explanation.
– Shawn
Dec 28 '18 at 17:30
add a comment |
1
I did not downvote, but code without any kind of explanation is only half of an answer.
– Peter B
Dec 28 '18 at 15:02
@PeterB Agreed, but to be fair, this change didn't really require a lot of explanation.
– Shawn
Dec 28 '18 at 17:30
1
1
I did not downvote, but code without any kind of explanation is only half of an answer.
– Peter B
Dec 28 '18 at 15:02
I did not downvote, but code without any kind of explanation is only half of an answer.
– Peter B
Dec 28 '18 at 15:02
@PeterB Agreed, but to be fair, this change didn't really require a lot of explanation.
– Shawn
Dec 28 '18 at 17:30
@PeterB Agreed, but to be fair, this change didn't really require a lot of explanation.
– Shawn
Dec 28 '18 at 17:30
add a comment |
2
Just replace it with "POSTED"in SELECT statement :
INSERT INTO AllPayandRec (BID, EntryDate, Typez, FullName, Assesable)
SELECT Bank.BID, Bank.EntryDate, Bank.Type, Bank.FullName, 'POSTED'
FROM Bank LEFT JOIN
AllPayandRec
ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS NULL;
edited Dec 28 '18 at 14:45
Johnny Bones
6,57552677
answered Dec 28 '18 at 14:34
Yogesh SharmaYogesh Sharma
28.8k51336
add a comment |
2
Just replace it with "POSTED"in SELECT statement :
INSERT INTO AllPayandRec (BID, EntryDate, Typez, FullName, Assesable)
SELECT Bank.BID, Bank.EntryDate, Bank.Type, Bank.FullName, 'POSTED'
FROM Bank LEFT JOIN
AllPayandRec
ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS NULL;
edited Dec 28 '18 at 14:45
Johnny Bones
6,57552677
answered Dec 28 '18 at 14:34
Yogesh SharmaYogesh Sharma
28.8k51336
add a comment |
2
2
2
Just replace it with "POSTED"in SELECT statement :
INSERT INTO AllPayandRec (BID, EntryDate, Typez, FullName, Assesable)
SELECT Bank.BID, Bank.EntryDate, Bank.Type, Bank.FullName, 'POSTED'
FROM Bank LEFT JOIN
AllPayandRec
ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS NULL;
edited Dec 28 '18 at 14:45
Johnny Bones
6,57552677
answered Dec 28 '18 at 14:34
Yogesh SharmaYogesh Sharma
28.8k51336
Just replace it with "POSTED"in SELECT statement :
INSERT INTO AllPayandRec (BID, EntryDate, Typez, FullName, Assesable)
SELECT Bank.BID, Bank.EntryDate, Bank.Type, Bank.FullName, 'POSTED'
FROM Bank LEFT JOIN
AllPayandRec
ON Bank.BID = AllPayandRec.BID
WHERE AllPayandRec.BID IS NULL;
edited Dec 28 '18 at 14:45
Johnny Bones
6,57552677
answered Dec 28 '18 at 14:34
Yogesh SharmaYogesh Sharma
28.8k51336
edited Dec 28 '18 at 14:45
Johnny Bones
6,57552677
edited Dec 28 '18 at 14:45
Johnny Bones
6,57552677
edited Dec 28 '18 at 14:45
Johnny Bones
6,57552677
6,57552677
answered Dec 28 '18 at 14:34
Yogesh SharmaYogesh Sharma
28.8k51336
answered Dec 28 '18 at 14:34
Yogesh SharmaYogesh Sharma
28.8k51336
answered Dec 28 '18 at 14:34
Yogesh SharmaYogesh Sharma
28.8k51336
28.8k51336
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%2f53960111%2finclude-hard-coded-value-in-insert-select%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
Did you miss part of the query? This shouldn't work because your
SELECTcolumns don't match up to yourINSERTcolumns (ie you are inserting 5 cols but only selecting 4). Other than that, just replaceBank.Assesablewith'POSTED'.– Shawn
Dec 28 '18 at 17:25