INSERT INTO temp 表在 SQL Server 中失败，但相同的语句在 PostgreSQL、MySQL 和 SQLite 中完美运行

Question

我们正在编写一个内部的"速度测试"程序，用于学习/培训目的(以及好奇心)，该程序可与几个后端配合使用-PostgreSQL、SQL Server、MySQL和；SQLite。

该程序在各自的后端创建一个临时表(这是主源表的一个简单副本)，因此我们可以对此测试UPDATE和INSERT命令(而不破坏主源数据)。它还创建了一个包含1000条记录的临时子集表。然后，它运行几个计时测试，对特定SQL操作的1000个子集记录进行迭代：SELECT、UPDATE和INSERT。

所有测试都在PostgreSQL、MySQL和&SQLite。SQL Server运行SELECT&UPDATE测试很好，但在所有1000个INSERT命令上都出错了——这些命令只指定了几个列/值。

(PostgreSQL和SQLite为INSERT命令中未指定的所有列分配了NULL值。MySQL仅为数据类型为DATE或TEXT的未指定列分配了空值。MySQL为未指定的数字列分配了0的空值，为"VarChar"列分配了一个空格字符串(长度正确)。)

SQL Server INSERT INTO在填充其主要源表方面工作得很好(我们在几个不同的表上重复测试)。但是这个过程为表中的每一列指定了一个值(主键除外)。

因此，我们怀疑INSERT操作在测试本身(只指定了几列)中的失败可能是由于SQL Server带来了"；不为空"；约束从主源表转移到临时表，但不是默认值。当然，这可能完全是由于其他原因。但不管怎样，我们都被难住了。

我们用于在SQL Server中(成功)创建大型临时表的SQL命令是：

select * into #TmpJobs from Jobs

所有其他后端都符合ANSI标准。因此，对于这些，我们使用命令：

create temporary table TmpJobs as select * from Jobs

SQL Server甚至拒绝以下非常简单的插入命令(如果字段名都是大写或小写，则没有区别)：

insert into #TmpJobs (JobCode) values ('J1')

我们正在使用SQLExpress 2019。当通过SSMS查看时，在管理>SQL Server日志>当前文件，提供有关失败INSERT命令的诊断信息。

有人能看到我们缺少了什么吗？并建议我们如何让它适用于简单的INSERT操作(不为所有字段指定值)？

11月5日添加：感谢您的回复"Dale K"；。正如你所建议的，我在SSMS中运行了以下内容，这提供了更有用的诊断信息：

Use AdminsoftTest
select * into #TmpJobs from AdminsoftApps.Jobs
insert into #TmpJobs (JobCode) values ('J1')

这给出了第3行的错误消息：

Cannot insert the value NULL into column 'ClientCode', table 'tempdb.dbo.#TmpJobs___...
; column does not allow nulls. INSERT fails.

看来；NOT NULL"；列条件确实已经转移到临时表，但没有转移到DEFAULT值。

有人知道将临时表的所有列设置为"0"的简单方法吗；可以为null的"？

(或者，有人知道有一个好的模板存储过程可以为临时表的所有列设置合理的空默认值(0表示数值等)吗？)

Answer 1

解决方案：添加一个"默认"；每列的表格约束

在应用程序中，我们添加了一个遍历临时表中所有字段名的循环，该循环运行SQL Server命令以添加一个"；默认"；表约束，主键列(RowID)除外。INSERT命令只指定了一些列，然后就完美地工作了。

注意：当我们尝试将默认值0设置为RowID时，SQL Server返回了一个错误。因此，这个(主键的)"RowID"表约束似乎是从源表复制过来的(除非SQL Server总是将名为"RowID"的列视为主键)。

以下是我们的应用程序中成功的SQL命令序列的摘要

select * into AdminsoftApps.#TmpJobs from AdminsoftApps.Jobs
alter table #TmpJobs add constraint DefaultJobCode default ' ' for JobCode
alter table #TmpJobs add constraint DefaultClientCode default ' ' for ClientCode
...  etc. (There are 45 fields in the Jobs table)

我们的应用程序的程序行(在1000条记录的循环中)随后起作用：

insert into AdminsoftApps.TmpJobs (JobCode, ClientCode, ...) values ( ... )

在应用程序的INSERT测试中，我们只为每个插入的记录指定了10列和值。在添加了新的默认表约束后，SQL Server接受了所有1000个INSERT命令，并在每个新记录中用它们的默认空值很好地填充了所有其他字段。

以下内容(仅在SELECT中指定几列)在SSMS中起作用：

Use AdminsoftTest
drop table #TmpJobs
select JobCode, JobName, ClientCode, AddrRecID into AdminsoftApps.#TmpJobs from AdminsoftApps.Jobs
alter table #TmpJobs add constraint DefaultJobCode default ' ' for JobCode
alter table #TmpJobs add constraint DefaultJobName default ' ' for JobName
alter table #TmpJobs add constraint DefaultClientCode default ' ' for ClientCode
alter table #TmpJobs add constraint DefaultAddrRecID default ' ' for AddrRecID
insert into AdminsoftApps.#TmpJobs (JobCode) values ('J1')

更广泛地了解NOT NULL列约束

SQL Server似乎会自动将NOT NULL约束应用于SELECT。。。进入。。。

在我们的测试中，它拒绝了"；ALTER TABLE ALTER COLUMN字段名NULL"尝试将其更改为NULL的语句。(当时这些列中的现有数据中没有NULL值。)

---

清除NOT NULL列约束的尝试失败：

下面这个"答案"的其余部分，只提供给那些有兴趣进一步阅读我们试图清除is NULL约束的人：

我们首先在SSMS中运行以下简单测试，以验证SQL Server是否自动将NOT NULL约束应用于SELECT。。。进入…：

Use AdminsoftTest
select '   ' as CharField1,  '   ' as CharField2, 100 as NumericField1
into #TmpTest from AdminsoftApps.Jobs where RowID <= 3
insert into #TmpTest (CharField1) values ('J1')

注意：SELECT中的所有列都是根据文字值创建的，没有一列是从源Jobs表中创建的，该表仅用于初始化具有3个空记录的#TmpTest。

这产生了以下错误消息：

(3 rows affected)
Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'CharField2', table 'AdminsoftTest.dbo.#TmpTest';
column does not allow nulls. INSERT fails.
The statement has been terminated.

因此，SQL Server似乎会自动将ISNULL约束应用于所有列，甚至是根据文字值(不在SELECT中指定的源表中)创建的新列。

SQL Server似乎也拒绝任何更改SELECT…创建的临时表的这些ISNULL列约束的尝试。。。变成。。。，因为以下也失败了：

Use AdminsoftTest
drop table #TmpTest
select '   ' as CharField1,  '   ' as CharField2, 100 as NumericField1
into #TmpTest from AdminsoftApps.Jobs where RowID <= 3
alter table #TmpTest alter column CharField2 NULL

错误消息是：

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'NULL'.

然而，上面第5行的语法似乎符合https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15(可能是我们在这方面搞砸了，如果有人能给我这个命令的正确语法，我将非常感谢反馈——SQL server接受并正确应用。)

使用SSMS进行测验(仅供参考)

我们惊喜地发现，我们可以在应用程序中使用它，因为我们使用SSMS进行的(简单得多的)测试非常不一致、不成功，而且令人困惑。

SSMS测试使用文字值创建了几个新列(并且源表中没有字段)。然而，那些混合了一个活动字段和从文字中创建的列的方法并不奏效。

以下是添加表约束的示例测试代码，并提供了一个在SSMS:中工作的INSERT示例

Use AdminsoftTest
select '   ' as CharField1,  '   ' as CharField2, 100 as NumericField1
into #TmpTest from AdminsoftApps.Jobs where RowID <= 3
alter table #TmpTest add constraint CharField2Default default ' ' for CharField2
alter table #TmpTest add constraint NumericField1Default default 0 for NumericField1
insert into #TmpTest (CharField1) values ('J1')

然而，出于某种原因，SQL Server使用命令来插入来自原始源表的字段：

Use AdminsoftTest
drop table #TmpTest
select JobCode, '   ' as CharField1,  '   ' as CharField2, 100 as NumericField1
into #TmpTest from AdminsoftApps.Jobs where RowID <= 3
alter table #TmpTest add constraint JobCodeDefault default ' ' for JobCode
alter table #TmpTest add constraint CharField1Default default ' ' for CharField1
alter table #TmpTest add constraint CharField2Default default ' ' for CharField2
alter table #TmpTest add constraint NumericField1Default default 0 for NumericField1
insert into #TmpTest (JobCode) values ('J1')

这产生了一个错误：

Msg 207, Level 16, State 1, Line 9
Invalid column name 'JobCode'.

然而，以下工作：

Use AdminsoftTest
drop table #TmpTest
select JobCode, '   ' as CharField1,  '   ' as CharField2, 100 as NumericField1
into #TmpTest from AdminsoftApps.Jobs where RowID <= 3
alter table #TmpTest add constraint JobCodeDefault default ' ' for JobCode
alter table #TmpTest add constraint CharField1Default default ' ' for CharField1
alter table #TmpTest add constraint CharField2Default default ' ' for CharField2
alter table #TmpTest add constraint NumericField1Default default 0 for NumericField1
insert into #TmpTest (CharField1) values ('J1')

这是非常不一致的。它在第5行接受"JobCode"列，但在第9行拒绝它。它接受根据字符串文字创建的新列的值，但不接受来自原始源表的列的值。如果有任何反馈可以解释为什么SSMS无法将值插入"JobCode"列(可能还有来自原始源表的临时表中的任何其他列)，我将不胜感激。

我希望这些额外的信息对某人有帮助。。。

问候Graeme Evans