SQL 如何在临时表中插入多个选择语句

How to insert multiple select statements into a temp table

提问by Dinesh Reddy Alla

I am having three tables with different data and i need to insert into one TEMP table and return that table in StoredProcedure.

我有三个包含不同数据的表，我需要插入一个 TEMP 表并在 StoredProcedure 中返回该表。

I tried as:

我试过：

-- To get last 10 Days Letters count
SELECT col1,col2,1 AS Type, LettersCount
INTO #temp FROM tblData

-- To get last 4 weeks Letters count
SELECT col1,col2,2 AS Type, LettersCount
INTO #temp FROM tblData

-- To get month wise Letters count
SELECT col1,col2,3 AS Type, LettersCount
INTO #temp FROM tblData

Showing Error as

将错误显示为

Msg 2714, Level 16, State 1, Line 16
There is already an object named '#temp ' in the database.
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near 'T'.
Msg 2714, Level 16, State 1, Line 32
There is already an object named '#temp ' in the database.

回答by Ganesh_Devlekar

You can Check it Already Exists or NOT

您可以检查它是否已经存在

IF OBJECT_ID ('tempdb..#TempLetters') is not null
drop table #TempLetters


SELECT col1,col2,1 AS Type, LettersCount
INTO #TempLetters FROM tblData

-- To get last 4 weeks Letters count
INSERT INTO #TempLetters
SELECT col1,col2,2 AS Type, LettersCount
FROM tblData

-- To get month wise Letters count
INSERT INTO #TempLetters
SELECT col1,col2,3 AS Type, LettersCount
FROM tblData

回答by Jonathan Leffler

Create the temporary table once, then insert into it for the other two SELECT statements:

创建临时表一次，然后为其他两个 SELECT 语句插入其中：

SELECT col1, col2, 1 AS Type, LettersCount
  INTO #temp
  FROM tblData;

INSERT INTO #temp
    SELECT col1, col2, 2 AS Type, LettersCount
      FROM tblData;

INSERT INTO #temp
    SELECT col1, col2, 3 AS Type, LettersCount
      FROM tblData;

回答by P?????

The SELECT INTOstatement can also be used to create a new, empty table using the schema of another select * into tablename from ..here tablenametable should not exist.

该SELECT INTO语句还可用于使用另一个不应存在​​的select * into tablename from ..此处tablename表的架构创建一个新的空表 。

Change your insert like this:

像这样改变你的插入：

SELECT col1,
       col2,
       1 AS Type,
       LettersCount
INTO   #temp
FROM   tblData

-- To get last 4 weeks Letters count
INSERT INTO #temp
SELECT col1,col2,2 AS Type,LettersCount
FROM   tblData

-- To get month wise Letters count
INSERT INTO #temp
SELECT col1,col2,3 AS Type,LettersCount
FROM   tblData 

回答by Hitesh

Why not write just a single insert statement and union the tables before insert

为什么不只写一个插入语句并在插入之前合并表

with A as
(
    -- To get last 10 Days Letters count
    SELECT col1,col2,1 AS Type, LettersCount
    FROM tblData
    union all
    -- To get last 4 weeks Letters count
    SELECT col1,col2,2 AS Type, LettersCount
    FROM tblData
    union all
    -- To get month wise Letters count
    SELECT col1,col2,3 AS Type, LettersCount
    FROM tblData
)
select col1, col2, Type, LettersCount
INTO #temp 
FROM A

This will help you add more tables in the select easily if you need as you wont need any more insert statements for them

如果您需要，这将帮助您轻松地在选择中添加更多表，因为您不再需要它们的插入语句

回答by Eduard Uta

The error occurs because the first select into statement creates the table and the second and third tries to recreate it again.

发生错误是因为第一个 select into 语句创建了表，而第二个和第三个尝试重新创建它。

Change the second and third queries into:

将第二个和第三个查询改为：

insert into #temp
select..