insert into temporary table not working in mysql stored procedure











up vote
0
down vote

favorite












i have the following stored procedure:



create temporary table logData (table_name varchar(50), column_name varchar(50), changed_date date, changed_by char(10), old_value varchar(50), new_value varchar(50))

insert into logData SELECT table_name, column_name, changed_date, changed_by, old_value, new_value FROM log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value FROM reservation_flight_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value FROM ticket_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value FROM passenger_record_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

select * from logData order by changed_date;


In the above sql select statement empId, startDate and endDate are the input parameters for this stored procedure. This is showing error please help? Thank You!










share|improve this question
























  • format MySQL code, instead of posting plain text
    – Dipak
    Nov 5 at 3:18










  • What error is it showing?
    – Nick
    Nov 5 at 3:34















up vote
0
down vote

favorite












i have the following stored procedure:



create temporary table logData (table_name varchar(50), column_name varchar(50), changed_date date, changed_by char(10), old_value varchar(50), new_value varchar(50))

insert into logData SELECT table_name, column_name, changed_date, changed_by, old_value, new_value FROM log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value FROM reservation_flight_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value FROM ticket_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value FROM passenger_record_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

select * from logData order by changed_date;


In the above sql select statement empId, startDate and endDate are the input parameters for this stored procedure. This is showing error please help? Thank You!










share|improve this question
























  • format MySQL code, instead of posting plain text
    – Dipak
    Nov 5 at 3:18










  • What error is it showing?
    – Nick
    Nov 5 at 3:34













up vote
0
down vote

favorite









up vote
0
down vote

favorite











i have the following stored procedure:



create temporary table logData (table_name varchar(50), column_name varchar(50), changed_date date, changed_by char(10), old_value varchar(50), new_value varchar(50))

insert into logData SELECT table_name, column_name, changed_date, changed_by, old_value, new_value FROM log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value FROM reservation_flight_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value FROM ticket_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value FROM passenger_record_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

select * from logData order by changed_date;


In the above sql select statement empId, startDate and endDate are the input parameters for this stored procedure. This is showing error please help? Thank You!










share|improve this question















i have the following stored procedure:



create temporary table logData (table_name varchar(50), column_name varchar(50), changed_date date, changed_by char(10), old_value varchar(50), new_value varchar(50))

insert into logData SELECT table_name, column_name, changed_date, changed_by, old_value, new_value FROM log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value FROM reservation_flight_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value FROM ticket_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value FROM passenger_record_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

select * from logData order by changed_date;


In the above sql select statement empId, startDate and endDate are the input parameters for this stored procedure. This is showing error please help? Thank You!







mysql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 5 at 3:32









Nick

17.9k41333




17.9k41333










asked Nov 5 at 2:54









Sagar Binod

52




52












  • format MySQL code, instead of posting plain text
    – Dipak
    Nov 5 at 3:18










  • What error is it showing?
    – Nick
    Nov 5 at 3:34


















  • format MySQL code, instead of posting plain text
    – Dipak
    Nov 5 at 3:18










  • What error is it showing?
    – Nick
    Nov 5 at 3:34
















format MySQL code, instead of posting plain text
– Dipak
Nov 5 at 3:18




format MySQL code, instead of posting plain text
– Dipak
Nov 5 at 3:18












What error is it showing?
– Nick
Nov 5 at 3:34




What error is it showing?
– Nick
Nov 5 at 3:34












1 Answer
1






active

oldest

votes

















up vote
0
down vote



accepted










I believe you are missing two semicolons: one at the end of the first statement and another at the end of the before-last line. You may rewrite it like the following:



CREATE temporary TABLE logData (table_name   varchar(50), 
column_name varchar(50),
changed_date date,
changed_by char(10),
old_value varchar(50),
new_value varchar(50));

INSERT INTO logData
SELECT table_name, column_name, changed_date, changed_by, old_value, new_value
FROM log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value
FROM reservation_flight_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value
FROM ticket_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value
FROM passenger_record_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate;

SELECT * FROM logData ORDER BY changed_date;





share|improve this answer





















  • I have used semicoloms also but it is not working but when i run without insert into statement it works but i need to store data in temporary table to sort them datewise.. please help me?
    – Sagar Binod
    Nov 5 at 11:31










  • What's the error message are you getting?
    – TeeKea
    Nov 5 at 14:46










  • i forget to use begin ... end statement inside the procedure and it was showing error. Now it worked. Next problem is when i execute the above procedure in phpmyadmin it displays result but when i execute from java it returns nothing.. Please help ..?
    – Sagar Binod
    Nov 5 at 14:58










  • Can you update your question please?
    – TeeKea
    Nov 5 at 15:01











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',
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


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53147723%2finsert-into-temporary-table-not-working-in-mysql-stored-procedure%23new-answer', 'question_page');
}
);

Post as a guest
































1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes








up vote
0
down vote



accepted










I believe you are missing two semicolons: one at the end of the first statement and another at the end of the before-last line. You may rewrite it like the following:



CREATE temporary TABLE logData (table_name   varchar(50), 
column_name varchar(50),
changed_date date,
changed_by char(10),
old_value varchar(50),
new_value varchar(50));

INSERT INTO logData
SELECT table_name, column_name, changed_date, changed_by, old_value, new_value
FROM log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value
FROM reservation_flight_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value
FROM ticket_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value
FROM passenger_record_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate;

SELECT * FROM logData ORDER BY changed_date;





share|improve this answer





















  • I have used semicoloms also but it is not working but when i run without insert into statement it works but i need to store data in temporary table to sort them datewise.. please help me?
    – Sagar Binod
    Nov 5 at 11:31










  • What's the error message are you getting?
    – TeeKea
    Nov 5 at 14:46










  • i forget to use begin ... end statement inside the procedure and it was showing error. Now it worked. Next problem is when i execute the above procedure in phpmyadmin it displays result but when i execute from java it returns nothing.. Please help ..?
    – Sagar Binod
    Nov 5 at 14:58










  • Can you update your question please?
    – TeeKea
    Nov 5 at 15:01















up vote
0
down vote



accepted










I believe you are missing two semicolons: one at the end of the first statement and another at the end of the before-last line. You may rewrite it like the following:



CREATE temporary TABLE logData (table_name   varchar(50), 
column_name varchar(50),
changed_date date,
changed_by char(10),
old_value varchar(50),
new_value varchar(50));

INSERT INTO logData
SELECT table_name, column_name, changed_date, changed_by, old_value, new_value
FROM log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value
FROM reservation_flight_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value
FROM ticket_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value
FROM passenger_record_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate;

SELECT * FROM logData ORDER BY changed_date;





share|improve this answer





















  • I have used semicoloms also but it is not working but when i run without insert into statement it works but i need to store data in temporary table to sort them datewise.. please help me?
    – Sagar Binod
    Nov 5 at 11:31










  • What's the error message are you getting?
    – TeeKea
    Nov 5 at 14:46










  • i forget to use begin ... end statement inside the procedure and it was showing error. Now it worked. Next problem is when i execute the above procedure in phpmyadmin it displays result but when i execute from java it returns nothing.. Please help ..?
    – Sagar Binod
    Nov 5 at 14:58










  • Can you update your question please?
    – TeeKea
    Nov 5 at 15:01













up vote
0
down vote



accepted







up vote
0
down vote



accepted






I believe you are missing two semicolons: one at the end of the first statement and another at the end of the before-last line. You may rewrite it like the following:



CREATE temporary TABLE logData (table_name   varchar(50), 
column_name varchar(50),
changed_date date,
changed_by char(10),
old_value varchar(50),
new_value varchar(50));

INSERT INTO logData
SELECT table_name, column_name, changed_date, changed_by, old_value, new_value
FROM log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value
FROM reservation_flight_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value
FROM ticket_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value
FROM passenger_record_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate;

SELECT * FROM logData ORDER BY changed_date;





share|improve this answer












I believe you are missing two semicolons: one at the end of the first statement and another at the end of the before-last line. You may rewrite it like the following:



CREATE temporary TABLE logData (table_name   varchar(50), 
column_name varchar(50),
changed_date date,
changed_by char(10),
old_value varchar(50),
new_value varchar(50));

INSERT INTO logData
SELECT table_name, column_name, changed_date, changed_by, old_value, new_value
FROM log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value
FROM reservation_flight_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value
FROM ticket_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value
FROM passenger_record_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate;

SELECT * FROM logData ORDER BY changed_date;






share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 5 at 3:50









TeeKea

1




1












  • I have used semicoloms also but it is not working but when i run without insert into statement it works but i need to store data in temporary table to sort them datewise.. please help me?
    – Sagar Binod
    Nov 5 at 11:31










  • What's the error message are you getting?
    – TeeKea
    Nov 5 at 14:46










  • i forget to use begin ... end statement inside the procedure and it was showing error. Now it worked. Next problem is when i execute the above procedure in phpmyadmin it displays result but when i execute from java it returns nothing.. Please help ..?
    – Sagar Binod
    Nov 5 at 14:58










  • Can you update your question please?
    – TeeKea
    Nov 5 at 15:01


















  • I have used semicoloms also but it is not working but when i run without insert into statement it works but i need to store data in temporary table to sort them datewise.. please help me?
    – Sagar Binod
    Nov 5 at 11:31










  • What's the error message are you getting?
    – TeeKea
    Nov 5 at 14:46










  • i forget to use begin ... end statement inside the procedure and it was showing error. Now it worked. Next problem is when i execute the above procedure in phpmyadmin it displays result but when i execute from java it returns nothing.. Please help ..?
    – Sagar Binod
    Nov 5 at 14:58










  • Can you update your question please?
    – TeeKea
    Nov 5 at 15:01
















I have used semicoloms also but it is not working but when i run without insert into statement it works but i need to store data in temporary table to sort them datewise.. please help me?
– Sagar Binod
Nov 5 at 11:31




I have used semicoloms also but it is not working but when i run without insert into statement it works but i need to store data in temporary table to sort them datewise.. please help me?
– Sagar Binod
Nov 5 at 11:31












What's the error message are you getting?
– TeeKea
Nov 5 at 14:46




What's the error message are you getting?
– TeeKea
Nov 5 at 14:46












i forget to use begin ... end statement inside the procedure and it was showing error. Now it worked. Next problem is when i execute the above procedure in phpmyadmin it displays result but when i execute from java it returns nothing.. Please help ..?
– Sagar Binod
Nov 5 at 14:58




i forget to use begin ... end statement inside the procedure and it was showing error. Now it worked. Next problem is when i execute the above procedure in phpmyadmin it displays result but when i execute from java it returns nothing.. Please help ..?
– Sagar Binod
Nov 5 at 14:58












Can you update your question please?
– TeeKea
Nov 5 at 15:01




Can you update your question please?
– TeeKea
Nov 5 at 15:01


















 

draft saved


draft discarded



















































 


draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53147723%2finsert-into-temporary-table-not-working-in-mysql-stored-procedure%23new-answer', 'question_page');
}
);

Post as a guest




















































































這個網誌中的熱門文章

Xamarin.form Move up view when keyboard appear

Post-Redirect-Get with Spring WebFlux and Thymeleaf

Anylogic : not able to use stopDelay()