SQL 如何正确 SELECT INTO @TempTable 多次返回单个结果集?
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/7152619/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I properly SELECT INTO @TempTable Multiple times to return a single resultset?
提问by nageeb
I'm trying to write a stored procedure for Crystal Reports by combining multiple queries into a single resultset (Crystal doesn't support multiple results in one report).
我正在尝试通过将多个查询组合到一个结果集中来为 Crystal Reports 编写一个存储过程(Crystal 不支持一个报告中的多个结果)。
The result set I'm trying to get combines columns from both tables.
我试图从两个表中获取组合列的结果集。
In the SP, I declare @temptable and the columns (because the two tables i'm querying have different columns).
在 SP 中,我声明了 @temptable 和列(因为我查询的两个表具有不同的列)。
DECLARE @TEMPNEWBILLING TABLE
(
ACCOUNT DECIMAL null,
CLIENT NVARCHAR null,
TIMESTAMP INT null,
BILLING DECIMAL null,
CALLKIND INT null,
HITK1 DECIMAL null,
HITK2 DECIMAL null,
HIDISC DECIMAL null,
HITALK DECIMAL null,
HIPTCH DECIMAL null,
HICONF DECIMAL null,
HIHOLD DECIMAL null,
PTCH DECIMAL null,
SUPERTIME DECIMAL null
)
I then SELECT
from both tables INTO
the temp table:
然后我SELECT
从两个表中INTO
获取临时表:
SELECT Account, Client, Timestamp, Billing, CallKind, HiTk1, HiTk2, HiDisc, HiTalk, HiPtch, HiConf, HiHold, Ptch
INTO TEMPNEWBILLING
FROM
mCallEnd
WHERE billing = cast(@BILLINGNUMBER as decimal)
AND Timestamp > @STARTITIME
AND Timestamp < @ENDITIME
AND CallKind in (0,1,2,3,4,16)
SELECT
Billing, SuperTime
INTO TEMPNEWBILLING
FROM
mClientMaint
WHERE billing = cast(@BILLINGNUMBER as decimal)
AND Timestamp > @STARTITIME
AND Timestamp < @ENDITIME
And finally, I just get all data from the temp table.
最后,我只是从临时表中获取所有数据。
SELECT * FROM @TEMPNEWBILLING
Unfortunately, something is going wrong, as when I run the SP, I get an error that
不幸的是,出了点问题,因为当我运行 SP 时,我收到一个错误
There is already an object named 'TEMPNEWBILLING' in the database.
数据库中已经有一个名为“TEMPNEWBILLING”的对象。
I've checked it out and it seems that the first query is running, but the error gets thrown at the second Select Into. I must be doing this wrong, as I get the same error if I use # tables or @ tables (i.e. delcare the table vs. create the table).
我已经检查过了,似乎第一个查询正在运行,但是在第二个 Select Into 处抛出错误。我一定是做错了,因为如果我使用#tables 或@tables(即delcare table vs. create table),我会得到同样的错误。
Is the prospect of filling a temp table with the results of two queries simply not possible? Am I using the wrong tool for the job?
用两个查询的结果填充临时表的前景根本不可能吗?我是否使用了错误的工具来完成这项工作?
回答by Lamak
In your code, you are not using the variable table youe defined, instead you are trying to put the results into the same physical table. Try this instead:
在您的代码中,您没有使用您定义的变量表,而是尝试将结果放入同一个物理表中。试试这个:
INSERT INTO @TEMPNEWBILLING(Account, Client, Timestamp, Billing, CallKind, HiTk1, HiTk2, HiDisc, HiTalk, HiPtch, HiConf, HiHold, Ptch)
SELECT Account, Client, Timestamp, Billing, CallKind, HiTk1, HiTk2, HiDisc, HiTalk, HiPtch, HiConf, HiHold, Ptch
FROM
mCallEnd
WHERE billing = cast(@BILLINGNUMBER as decimal)
AND Timestamp > @STARTITIME
AND Timestamp < @ENDITIME
AND CallKind in (0,1,2,3,4,16)
INSERT INTO @TEMPNEWBILLING(Billing, SuperTime)
SELECT
Billing, SuperTime
FROM
mClientMaint
WHERE billing = cast(@BILLINGNUMBER as decimal)
AND Timestamp > @STARTITIME
AND Timestamp < @ENDITIME
回答by Joel Beckham
SELECT... INTO creates a new table.
SELECT... INTO 创建一个新表。
You'll want to reverse it:
你会想要扭转它:
INSERT INTO @TEMPNEWBILLING
(Columns...)
SELECT (your select query here)
You'll want to declare the table (technically it's a table variable since you're using the @ sign) as you did. Then use INSERT INTO... SELECT... for all of your inserts.
您需要像之前一样声明表(从技术上讲,它是一个表变量,因为您使用了 @ 符号)。然后对所有插入使用 INSERT INTO... SELECT... 。
回答by Tom H
You need to use INSERT
once the table is already created. Also, you're using a table variable, so you need to include the @ at the beginning of the name when referring to it. Since you're declaring the table variable at the start, both statements should actually be INSERT
and not SELECT INTO
.
INSERT
一旦表已经创建,您需要使用。此外,您使用的是表变量,因此在引用它时需要在名称的开头包含 @。由于您在开始时声明了 table 变量,因此两个语句实际上都应该是INSERT
而不是SELECT INTO
。
SELECT INTO
tries to create a new table. In your code you basically declare a table variable (which never gets used), then your first SELECT INTO
creates a permanent table with the name TEMPNEWBILLING
, then your second SELECT INTO
tries to create a table with the same exact name - hence the error.
SELECT INTO
尝试创建一个新表。在您的代码中,您基本上声明了一个表变量(从未使用过),然后您首先SELECT INTO
创建一个名为 name 的永久表TEMPNEWBILLING
,然后您的第二个SELECT INTO
尝试创建一个具有相同名称的表 - 因此出现错误。