如何将多个select语句插入临时表

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.

5 个解决方案

#1

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

#2

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;

#3

The SELECT INTO statement can also be used to create a new, empty table using the schema of another select * into tablename from .. here tablename table 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

#4

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

#5

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

为什么不在insert之前只编写一个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

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

#1