如何将存储过程中的数据获取到临时表中？

发布于 2024-07-06 06:30:37 字数 124 浏览 5 评论 0原文

我正在使用 sybase ASE 15。寻找类似的内容

Select * into #tmp exec my_stp;

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

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

指尖上得阳光 2024-07-13 06:30:37

在 ASE 15 中，我相信您可以使用函数，但它们对多行数据集没有帮助。

如果您的存储过程使用“从某处选择 col1,col2”返回数据，则无法获取该数据，它只会流回客户端。

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


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

运行代码：


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

exec my_stp
go

select * from #mine
drop table #mine
go

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

回复收藏 0 原文

假情假意假温柔 2024-07-13 06:30:37

我刚刚遇到了这个问题，迟到总比不到好……

这是可行的，但是屁股上巨大的痛苦，涉及 Sybase“代理表" 代表另一个本地或远程对象（表、过程，看法）。以下内容在 12.5 中有效，希望较新的版本有更好的方法。

假设您有一个存储过程定义为：

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

首先切换到 tempdb：

use tempdb

然后创建一个代理表，其中的列与结果集匹配：

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"

注意点：

“loopback”是 Sybase 的等效项
本地主机，但你可以替换
它适用于在以下位置注册的任何服务器
服务器的 sysservers 表。
当 Sybase 执行存储过程时，_timestamp 参数将转换为 @timestamp，并且像这样声明的所有参数列必须定义为 null。

然后，您可以从您自己的数据库中的表中进行选择：

declare @myTimestamp datetime
set @myTimestamp = getdate()

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

这很简单。要插入临时表，请先创建它：

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

然后组合：

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

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.

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:

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

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

回复收藏 0 原文

昇り龍 2024-07-13 06:30:37

当我寻找将 sys proc (sp_spaceused) 的结果集保存在持久表中的解决方案时，我发现了这个问题。

您可以让存储过程将结果写入临时表。（就像 sp_spaceused 一样。）

select name = @tabname
             , rowtotal = convert(varchar(30), @rowcount)
             , reserved = convert(varchar(30), (@reserved_pgs * @pgsize_KB)) + " KB"
             , data     = convert(varchar(30), (@dataonly_pgs * @pgsize_KB)) + " KB"
             , index_size = convert(varchar(30), (@index_pgs * @pgsize_KB)) + " KB"
             , unused = convert(varchar(30), (@unused_pgs * @pgsize_KB)) + " KB"
        into #fmtpgcounts

这里的答案确实有助于找到我的解决方案。
我使用 sp_helptext & 从 sys procs 创建了自己的存储过程。复制+粘贴。

use sybsystemprocs
go
/*
**my_storedproc_spaceused.sql => my_spaceused
*/
sp_helptext sp_spaceused
go
/*
**my_storedproc_my_f_getbigint.sql => my_f_getbigint
*/
sp_helptext sp_f_getbigint
go
/*
**my_storedproc_my_f_getuint.sql => my_f_getuint
*/
sp_helptext sp_f_getuint
go
/*
**my_storedproc_my_f_getval.sql => my_f_getval
*/
sp_helptext sp_f_getval
go

然后我让 my_spaceused 将结果集保存到持久表中。

select name = @tabname
             , rowtotal = convert(varchar(30), @rowcount)
             , reserved = convert(varchar(30), (@reserved_pgs * @pgsize_KB)) + " KB"
             , data     = convert(varchar(30), (@dataonly_pgs * @pgsize_KB)) + " KB"
             , index_size = convert(varchar(30), (@index_pgs * @pgsize_KB)) + " KB"
             , unused = convert(varchar(30), (@unused_pgs * @pgsize_KB)) + " KB"
        into #fmtpgcounts /* original from sp_spaceused */
        select * into mb_res_my_spaceused from #fmtpgcounts /* my line */
        exec sp_autoformat #fmtpgcounts /* original from sp_spaceused */

之后，我将持久结果表中的数据写入名称上与测量表相关的表中，因为每次运行存储过程时，结果表都会被重写。

my_spaceused table_to_masure
go
select * into mb_alt_space_table_to_masure from mb_res_my_spaceused
go

I found this question, when I was searching for a solution to save the result set of a sys proc (sp_spaceused) in a persistent table.

You can let the stored proc just let the result write into a temp table. (Just like sp_spaceused does.)

select name = @tabname
             , rowtotal = convert(varchar(30), @rowcount)
             , reserved = convert(varchar(30), (@reserved_pgs * @pgsize_KB)) + " KB"
             , data     = convert(varchar(30), (@dataonly_pgs * @pgsize_KB)) + " KB"
             , index_size = convert(varchar(30), (@index_pgs * @pgsize_KB)) + " KB"
             , unused = convert(varchar(30), (@unused_pgs * @pgsize_KB)) + " KB"
        into #fmtpgcounts

The answers here really helped finding my solution.
I created my own stored procs out of the sys procs using sp_helptext & copy+paste.

use sybsystemprocs
go
/*
**my_storedproc_spaceused.sql => my_spaceused
*/
sp_helptext sp_spaceused
go
/*
**my_storedproc_my_f_getbigint.sql => my_f_getbigint
*/
sp_helptext sp_f_getbigint
go
/*
**my_storedproc_my_f_getuint.sql => my_f_getuint
*/
sp_helptext sp_f_getuint
go
/*
**my_storedproc_my_f_getval.sql => my_f_getval
*/
sp_helptext sp_f_getval
go

Then I made my_spaceused saving the result set into a persistent table.

select name = @tabname
             , rowtotal = convert(varchar(30), @rowcount)
             , reserved = convert(varchar(30), (@reserved_pgs * @pgsize_KB)) + " KB"
             , data     = convert(varchar(30), (@dataonly_pgs * @pgsize_KB)) + " KB"
             , index_size = convert(varchar(30), (@index_pgs * @pgsize_KB)) + " KB"
             , unused = convert(varchar(30), (@unused_pgs * @pgsize_KB)) + " KB"
        into #fmtpgcounts /* original from sp_spaceused */
        select * into mb_res_my_spaceused from #fmtpgcounts /* my line */
        exec sp_autoformat #fmtpgcounts /* original from sp_spaceused */

Afterwards I wrote the data from the persitent result table into a table relating to the masured table in name, because everytime, I run the stored proc, the result table will be rewritten.

my_spaceused table_to_masure
go
select * into mb_alt_space_table_to_masure from mb_res_my_spaceused
go

回复收藏 0 原文

菊凝晚露 2024-07-13 06:30:37

不确定 Sybase，但在 SQL Server 中，以下操作应该有效：

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

回复收藏 0 原文

我做我的改变 2024-07-13 06:30:37

如果 my_stp 通过计算不同表中的值来填充数据，您可以创建一个与 my_stp 完全相同的等效视图。

CREATE VIEW My_view
 AS
/*
  My_stp body
*/


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

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.

CREATE VIEW My_view
 AS
/*
  My_stp body
*/


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

回复收藏 0 原文

~没有更多了~

关于作者

沙与沫

暂无简介

0 文章

0 评论

431 人气

关注发私信

友情链接

文江博客

如何将存储过程中的数据获取到临时表中？

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（5）

关于作者

相关话题

热门标签

推荐作者

马化腾

thousandcents

辰『辰』

ailin001

再摆5分钟就干活

冷情妓

友情链接

如何将存储过程中的数据获取到临时表中？

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（5）

关于作者

相关话题

热门标签

推荐作者

马化腾

thousandcents

辰『辰』

ailin001

再摆5分钟就干活

冷情妓

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。