如何将存储过程中的数据放入临时表? [英] How can I get data from a stored procedure into a temp table?

问题描述

我正在研究 sybase ASE 15.正在寻找这样的东西

Select * into #tmp exec my_stp;

my_stp 返回 10 个数据行，每行两列.

解决方案

在 ASE 15 中，我相信您可以使用函数，但它们不会帮助处理多行数据集.

如果您的存储过程返回带有从某处选择 col1,col2"的数据，则无法获取该数据，它只会流回客户端.

您可以做的是将数据直接插入到临时表中.这可能有点棘手，就像您在 sproc 中创建临时表一样，一旦 sproc 完成运行，它就会被删除并且您看不到内容.这样做的技巧是在 sproc 之外创建临时表，但要从 sproc 中引用它.这里的难点是每次重新创建 sproc 时都必须创建临时表，否则会出现找不到表"错误.

<预><代码>--您必须使用整个脚本来重新创建 sproc创建表#mine(col1 varchar(3),col2 varchar(3))去创建过程 my_stp作为插入#mine值(aaa"，aaa")插入#mine values("bbb","bbb")插入#mine values("ccc","ccc")插入#mine values("ccc","ccc")去删除表#mine去

运行代码:

<预><代码>创建表#mine(col1 varchar(3),col2 varchar(3))去执行 my_stp去从#mine中选择*删除表#mine去

Am working on sybase ASE 15. Looking for something like this

Select * into #tmp exec my_stp;

my_stp returns 10 data rows with two columns in each row.

解决方案

In ASE 15 I believe you can use functions, but they're not going to help with multirow datasets.

If your stored proc is returning data with a "select col1,col2 from somewhere" then there's no way of grabbing that data, it just flows back to the client.

What you can do is insert the data directly into the temp table. This can be a little tricky as if you create the temp table within the sproc it is deleted once the sproc finishes running and you don't get to see the contents. The trick for this is to create the temp table outside of the sproc, but to reference it from the sproc. The hard bit here is that every time you recreate the sproc you must create the temp table, or you'll get "table not found" errors.

    --You must use this whole script to recreate the sproc    
    create table #mine
    (col1 varchar(3),
    col2 varchar(3))
    go
    create procedure my_stp
    as
    insert into #mine values("aaa","aaa")
    insert into #mine values("bbb","bbb")
    insert into #mine values("ccc","ccc")
    insert into #mine values("ccc","ccc")
    go
    drop table #mine
    go

The to run the code:

create table #mine
(col1 varchar(3),
col2 varchar(3))
go

exec my_stp
go

select * from #mine
drop table #mine
go

这篇关于如何将存储过程中的数据放入临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！