如何将存储过程中的数据导入临时表？

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

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

Select * into #tmp exec my_stp;

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

my_stp返回10个数据行,每行有两列。

4 个解决方案

#1

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

在ASE 15中,我相信你可以使用函数,但它们不会帮助多行数据集。

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.

如果你的存储过程返回带有“select col1,col2 from somewhere”的数据,那么就无法获取该数据,它只会流回客户端。

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.

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


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

#2

I've just faced this problem, and better late than never...

我刚遇到这个问题,迟到总比没有好......

It's doable, but a monstrous pain in the butt, involving a Sybase "proxy table" which is a standin for another local or remote object (table, procedure, view). The following works in 12.5, newer versions hopefully have a better way of doing it.

它是可行的,但是在对接中有一个巨大的痛苦,涉及一个Sybase“代理表”,它是另一个本地或远程对象(表,过程,视图)的代表。以下工作在12.5中,较新的版本希望有更好的方法。

Let's say you have a stored proc defined as:

假设您将存储过程定义为:

create procedure mydb.mylogin.sp_extractSomething (
@timestamp datetime) as
select column_a, column_b
    from sometable
    where timestamp = @timestamp

First switch to the tempdb:

首先切换到tempdb:

use tempdb

Then create a proxy table where the columns match the result set:

然后创建一个代理表,其中列与结果集匹配:

create existing table myproxy_extractSomething (
column_a int not null, -- make sure that the types match up exactly!
column_b varchar(20) not null,
_timestamp datetime null,
primary key (column_a)) external procedure at "loopback.mydb.mylogin.sp_extractSomething"

Points of note:

注意事项:

"loopback" is the Sybase equivalent of localhost, but you can substitute it for any server registered in the server's sysservers table.

“loopback”是Sybase等效的localhost,但您可以将其替换为在服务器的sysservers表中注册的任何服务器。

The _timestamp parameter gets translated to @timestamp when Sybase executes the stored proc, and all parameter columns declared like this must be defined as null.

当Sybase执行存储过程时,_timestamp参数将转换为@timestamp,并且必须将声明为此类的所有参数列定义为null。

You can then select from the table like this from your own db:

然后,您可以从您自己的数据库中选择这样的表:

declare @myTimestamp datetime
set @myTimestamp = getdate()

select * 
from tempdb..myproxy_extractSomething
where _timestamp = @myTimestamp

Which is straightforward enough. To then insert into a temporary table, create it first:

这很简单。然后插入临时表,首先创建它:

create table #myTempExtract (
    column_a int not null, -- again, make sure that the types match up exactly
    column_b varchar(20) not null,
    primary key (column_a)
)

and combine:

insert into #myTempExtract (column_a, column_b)
select column_a, column_b
    from tempdb..myproxy_extractSomething
    where _timestamp = @myTimestamp

#3

-1

Not sure about Sybase, but in SQL Server the following should work:

不确定Sybase,但在SQL Server中应该有以下工作:

INSERT INTO #tmp (col1,col2,col3...) exec my_stp

INSERT INTO #tmp(col1,col2,col3 ...)exec my_stp

#4

-1

If my_stp is populating data by computing values from different tables, you can create an equivalent view which does exactly the same as my_stp.

如果my_stp通过计算来自不同表的值来填充数据,则可以创建与my_stp完全相同的等效视图。

CREATE VIEW My_view
 AS
/*
  My_stp body
*/


Then select data from view 
SELECT *  INTO #x FROM my_view

#1