db2等价于tsql的临时表 [英] db2 equivalent of tsql temp table

问题描述

如何在DB2中执行以下TSQL查询？我根据查询结果创建临时表时遇到问题。

How would I do the following TSQL query in DB2? I'm having problems creating a temp table based on the results from a query.

SELECT 
COLUMN_1, COLUMN_2, COLUMN_3
INTO #TEMP_A
FROM TABLE_A
WHERE COLUMN_1 = 1 AND COLUMN_2 = 2

错误消息是：

错误：SQL0104N后发现意外令牌#TEMP_A令牌可能包括：：。SQLSTATE = 42601

"Error: SQL0104N An unexpected token "#TEMP_A" was found following "". Expected tokens may include: ":". SQLSTATE=42601"

推荐答案

href =http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0003272.html>声明临时表在DB2中可以使用它：

You have to declare a temp table in DB2 before you can use it:

DECLARE GLOBAL TEMPORARY TABLE SESSION.YOUR_TEMP_TABLE_NAME AS (
    SELECT COLUMN_1, COLUMN_2, COLUMN_3
    FROM TABLE_A
) DEFINITION ONLY

然后填充它： / p>

Then populate it:

INSERT INTO SESSION.YOUR_TEMP_TABLE_NAME
SELECT COLUMN_1, COLUMN_2, COLUMN_3
FROM TABLE_A
WHERE COLUMN_1 = 1
  AND COLUMN_2 = 2

它不像SQL Server那么直观。即使它被称为全局临时表，它只存在于当前会话中。：）

It's not quite as straight-forward as in SQL Server. :)

请注意，所有临时表应以 SESSION 模式为前缀。如果您没有提供模式名称，则将暗示 SESSION 。

And even though it's called a "global" temporary table, it only exists for the current session. Note that all temp tables should be prefixed with the SESSION schema. If you do not provide a schema name, then SESSION will be implied.

这篇关于db2等价于tsql的临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！