创建公用表表达式 - 临时表 - 使用TSQL ??? [英] Creating a common table expression--temporary table--using TSQL???

问题描述

对DB2表使用SQL''with''关键字用于

动态创建一个临时表，其中包含一个SQL语句

该SQL语句的持续时间。

与使用TSQL的SQL''''等效的是什么？如果没有

one，那么创建与SQL语句关联的临时表（即
）的TSQL解决方案是什么？示例将不胜感激。

谢谢!!

Using SQL against a DB2 table the ''with'' key word is used to
dynamically create a temporary table with an SQL statement that is
retained for the duration of that SQL statement.
What is the equivalent to the SQL ''with'' using TSQL? If there is not
one, what is the TSQL solution to creating a temporary table that is
associated with an SQL statement? Examples would be appreciated.
Thank you!!

推荐答案

2004年12月28日07:07： 49 -0800， ra***********@ins.state。 il.us 写道：

On 28 Dec 2004 07:07:49 -0800, ra***********@ins.state.il.us wrote:

对DB2表使用SQL''with''关键字用于动态创建带有SQL的临时表在SQL语句的持续时间内保留的语句。
使用TSQL的SQL与'''等价的是什么？如果没有，那么创建与SQL语句关联的临时表的TSQL解决方案是什么？例子将不胜感激。
谢谢!!

Using SQL against a DB2 table the ''with'' key word is used to
dynamically create a temporary table with an SQL statement that is
retained for the duration of that SQL statement.
What is the equivalent to the SQL ''with'' using TSQL? If there is not
one, what is the TSQL solution to creating a temporary table that is
associated with an SQL statement? Examples would be appreciated.
Thank you!!

我相信在SQL Server 2005中有这样的东西，但在之前的任何时候都没有

版本。

I believe there is such a thing in SQL Server 2005, but not in any earlier
versions.

你没有说你在哪个版本的MSSQL，所以我假设2000.

这是直接的TSQL书籍。


临时表

SQL Server支持临时表。这些表的名称为

以数字符号（＃）开头。如果在用户断开连接时没有删除临时表，SQL Server会自动删除临时表。

临时表不存储在当前数据库中;它们是存储在tempdb系统数据库中的
。

有两种类型的临时表：

本地临时表

这些表的名称以一个数字符号（＃）开头。这些表

仅对创建它们的连接可见。

全局临时表

这些表的名称以两个数字符号开头（ ##）。这些

表对所有连接都可见。如果在创建连接的连接断开之前没有明确删除表格，那么只要所有其他任务停止引用它们，它们就会被删除。没有新的

任务可以在连接之后引用一个全局临时表，即

创建它断开连接。当前语句完成执行时，任务和表之间的关联是总是被删除;因此，
因此，全局临时表通常会在
$之后很快删除创建它们的b $ b连接断开连接。

临时表的许多传统用法现在可以用具有表数据类型的

变量替换。



示例

创建表#TempTable（col1 varchar（10），col2位）

插入#TempTable值（''asdf ''，1）

select * from #TempTable

select * into＃TempTable2 from #TempTable

select * from＃TempTable2

drop table #TempTable

drop table＃TempTable2

你可以使用临时表来做任何事情你可以用

普通表，包括索引。

HTH

Paul

You did not say what version of MSSQL you are on so I will assume 2000.
This is straight from the TSQL books.

Temporary Tables
SQL Server supports temporary tables. These tables have names that
start with a number sign (#). If a temporary table is not dropped when
a user disconnects, SQL Server automatically drops the temporary table.
Temporary tables are not stored in the current database; they are
stored in the tempdb system database.
There are two types of temporary tables:
Local temporary tables
The names of these tables begin with one number sign (#). These tables
are visible only to the connection that created them.
Global temporary tables
The names of these tables begin with two number signs (##). These
tables are visible to all connections. If the tables are not dropped
explicitly before the connection that created them disconnects, they
are dropped as soon as all other tasks stop referencing them. No new
tasks can reference a global temporary table after the connection that
created it disconnects. The association between a task and a table is
always dropped when the current statement completes executing;
therefore, global temporary tables are usually dropped soon after the
connection that created them disconnects.
Many traditional uses of temporary tables can now be replaced with
variables that have the table data type.


Example
create table #TempTable (col1 varchar(10), col2 bit)
insert into #TempTable values(''asdf'', 1)
select * from #TempTable
select * into #TempTable2 from #TempTable
select * from #TempTable2
drop table #TempTable
drop table #TempTable2
You can just about anything with a temp table that you can with a
normal table, including indexes.
HTH
Paul

是的，但我认为他想要将声明与名称相关联，a

物理表 - 就像一个临时视图。


2004年12月28日07:19:13 -0800，保罗 < ST ******* @ yahoo.com>写道：

Yes, but I think he wanted to associate the statement with the name, a
physical table - like a temporary view.

On 28 Dec 2004 07:19:13 -0800, "Paul" <st*******@yahoo.com> wrote:

你没有说你在哪个版本的MSSQL，所以我假设2000.
这是直接来自TSQL书籍。
临时表
SQL Server支持临时表。这些表的名称以数字符号（＃）开头。如果在用户断开连接时未删除临时表，SQL Server将自动删除临时表。
临时表不存储在当前数据库中;它们存储在tempdb系统数据库中。
有两种类型的临时表：
本地临时表
这些表的名称以一个数字符号（＃）开头。这些表仅对创建它们的连接可见。
全局临时表
这些表的名称以两个数字符号（##）开头。所有连接都可以看到这些表。如果在创建表的连接断开之前未明确删除表，则只要所有其他任务停止引用它们，它们就会被删除。在创建连接的连接断开后，没有新的
任务可以引用全局临时表。当前语句完成执行时，任务和表之间的关联总是被删除;因此，全局临时表通常在创建它们的
连接断开后立即被删除。
临时表的许多传统用法现在可以用具有表数据类型的变量替换。


创建表#TempTable（col1 varchar（10） ），col2 bit）
插入#TempTable值（''asdf''，1）
select * from #TempTable
select * into＃TempTable2 from #TempTable
select *来自＃TempTable2
drop table #TempTable
drop table＃TempTable2

你可以使用临时表，你可以使用
普通表，包括索引。

HTH
保罗

You did not say what version of MSSQL you are on so I will assume 2000.
This is straight from the TSQL books.

Temporary Tables
SQL Server supports temporary tables. These tables have names that
start with a number sign (#). If a temporary table is not dropped when
a user disconnects, SQL Server automatically drops the temporary table.
Temporary tables are not stored in the current database; they are
stored in the tempdb system database.
There are two types of temporary tables:
Local temporary tables
The names of these tables begin with one number sign (#). These tables
are visible only to the connection that created them.
Global temporary tables
The names of these tables begin with two number signs (##). These
tables are visible to all connections. If the tables are not dropped
explicitly before the connection that created them disconnects, they
are dropped as soon as all other tasks stop referencing them. No new
tasks can reference a global temporary table after the connection that
created it disconnects. The association between a task and a table is
always dropped when the current statement completes executing;
therefore, global temporary tables are usually dropped soon after the
connection that created them disconnects.
Many traditional uses of temporary tables can now be replaced with
variables that have the table data type.


Example
create table #TempTable (col1 varchar(10), col2 bit)
insert into #TempTable values(''asdf'', 1)
select * from #TempTable
select * into #TempTable2 from #TempTable
select * from #TempTable2
drop table #TempTable
drop table #TempTable2
You can just about anything with a temp table that you can with a
normal table, including indexes.
HTH
Paul

这篇关于创建公用表表达式 - 临时表 - 使用TSQL ???的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！