How to generate a new table during execution of a stored procedure

qiuhaozhu

A table is generated when the stored procedure exec sel-all is executed. How can I perform operations on this table?
My stored procedure is as follows:
CREATE proc sel_all as
select l_no, type = 'picking list', date, danhao, dept, glno, wlbh, ​​l_name, quality, sysdate from linliao
union
select t_id, type = 'Return bill', t_date, danhao, dept, orderno, wlbh, ​​t_name, store sysdate from tuiliao

I also need to operate on the table generated after execution, or let it generate a new table after execution

我是杀人狂

insert into newtable select * from (
select l_no, type = 'Pickup list', date, danhao, dept, glno, wlbh, ​​l_name, quality, sysdate from linliao
union
select t_id, type = 'return form', t_date, danhao, dept, orderno, wlbh, ​​t_name, store sysdate from tuiliao) a

prajna

select * into table1 from (select l_no, type = 'picking list', date, danhao, dept, glno, wlbh, ​​l_name, quality, sysdate from linliao
union
select t_id, type = 'return form', t_date, danhao, dept, orderno, wlbh, ​​t_name, store sysdate from tuiliao) t

fanfan1980

insert newTbName exec sel_all

hiqwolfzzh

Correct,
select * into table name from
(.....................) alias

cv4bf5d65fd

alter proc sel_all
as

insert into tablename select * from (
select l_no, type = 'request list', date, danhao, dept, glno, wlbh, ​​l_name, quality, sysdate from linliao
union
select t_id, type = 'return form', t_date, danhao, dept, orderno, wlbh, ​​t_name, store, sysdate from tuiliao) T

qiuhaozhu

I need to call this stored procedure repeatedly. Is it possible to generate a temporary table that will automatically disappear after being used up? A new one will be generated the next time it is called

wgfxman

#Temporary table name

Before the table name +'#' will be automatically recovered after the connection is over