如何两次插入临时表

Question

我学习了以下类似的SQL语句：

IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE name Like N'#tmp%'
 and id=object_id('tempdb..#tmp'))
DROP TABLE #tmp

into #tmp
select * from permTable

在继续处理之前，我需要向 #tmp 添加更多数据：

insert into #tmp
select * from permTable2

但是这会导致错误，因为SQL已经假定了#tmp列的大小和类型（例如，如果permTable中有一个int的列，但是permTable2中有相同名称的列，但是其中一条记录为NULL，则会出现“无法将值NULL插入到列'IsPremium'，表'tempdb.dbo.#tmp'中”）。

我该如何让#tmp拥有我想要的类型？这真的是不好的实践吗？

- Patrick

---

你是说permTable和permTable2有不同的模式吗？ - Michael Goldshteyn

它们并不存在，因为这只是一个简化的例子，但为了这个例子的目的，这些表将具有相同的模式。 - Patrick

顺便说一句 - 我会避免使用 select * into - 这将使源表的任何更改成为破坏性更改。 - Christoph

你能在两个SELECT语句之间使用UNION ALL来一次性插入它们吗？这将创建一个具有正确列设置的临时表。 - JNK

Answer 1

您考虑过创建一个表变量吗？您可以像这样声明列。

declare @sometable table(
     SomeField [nvarchar](15),
     SomeOtherField [decimal](15,2));

- Christoph

---

很酷，看起来不错。sometable在相同的位置创建，具有与 #tmp 相同的生命周期等吗？ - Patrick

表变量在存储过程或函数结束时会超出范围，而临时表在会话结束时会超出范围。请参阅http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html以获取更多详细信息。 - Christoph

Answer 2

这就是为什么对于您的问题，使用select into是一个糟糕的主意。使用create table命令创建特定的表结构，然后编写两个insert语句。

- HLGEM

Answer 3

这是不可能的。

如果您现在需要生成表定义类型列表，请从选择语句创建视图，并从information_schema中读取列及其定义...（此艺术品不考虑decimal和/或datetime2）

注意：这将为您当前选择的varchar/varbinary列提供最低可能的字段长度。
您需要手动调整它们...

SELECT 
      ','
    + COLUMN_NAME 
    + ' ' 
    + DATA_TYPE
    + ' '
    + ISNULL
    (
          '(' 
          + 
          CASE 
            WHEN CHARACTER_MAXIMUM_LENGTH = -1 
                THEN 'MAX' 
            ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS varchar(36)) 
            END 
        + ')' 
        , '' 
    ) 
    + ' '
    + CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE '' END 
FROM information_schema.columns 
WHERE table_name = '________theF'
ORDER BY ORDINAL_POSITION

插入语句的字段列表：

SELECT 
      ',' + COLUMN_NAME 
FROM information_schema.columns 
WHERE table_name = '________theF'
ORDER BY ORDINAL_POSITION 

- Stefan Steiger