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

Question

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

我试过:

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

错误显示为

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.

Answer 1

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

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