sql-server - azure sql中带有OUTPUT子句的删除语句

Question

我正在使用 DELETE 语句的 OUTPUT 子句插入到存档表中。

此代码在我的本地数据库上运行没有问题。但是，在 Azure 数据库上运行时出现错误，因为列值为空。

为什么这在 Azure 上的表现会有所不同？

这段代码:

DECLARE @courseId uniqueidentifier
SET @courseId = 'c7f5a926-e77a-4465-9120-38da284ed7d5'

DECLARE @courseCode nvarchar(max)
SELECT @courseCode = CourseCode FROM Courses WHERE CourseId = @courseId

SELECT @courseId
SELECT @courseCode

-- Courses
DELETE Courses
OUTPUT DELETED.*
INTO Archived_Courses
WHERE CourseId = @courseId

返回此错误:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Msg 515, Level 16, State 2, Line 16 Cannot insert the value NULL into column 'CourseName', table '...dbo.Archived_Courses'; column does not allow nulls. INSERT fails. The statement has been terminated.

Answer 1

我做了一个报告，他实际上在我的 SQL Azure 数据库上工作得很好:假设你有类似的东西:

Create Table Courses
(
    CourseId uniqueidentifier NOT NULL PRIMARY KEY DEFAULT newid(),
    CourseCode nvarchar(50) NOT NULL DEFAULT newid(),
    CourseDescription nvarchar(MAX) NULL
)  

Create Table Archived_Courses
(
    CourseId uniqueidentifier NOT NULL PRIMARY KEY ,
    CourseCode nvarchar(50) NOT NULL ,
    CourseDescription nvarchar(MAX) NULL
)  

这是您的解决方案，对我有用(唯一的区别，我通过类(class)代码上的简单选择获得了唯一标识符)

Insert into Courses (CourseCode, CourseDescription) values ('MOD01', 'Module 1 description');

DECLARE @courseId uniqueidentifier
Select @courseId = CourseId from Courses where CourseCode = 'MOD01'

DECLARE @courseCode nvarchar(max)
SELECT @courseCode = CourseCode FROM Courses WHERE CourseId = @courseId

SELECT @courseId
SELECT @courseCode

-- Courses
DELETE Courses
OUTPUT DELETED.*
INTO Archived_Courses
WHERE CourseId = @courseId

您确定在本地数据库中插入时没有生成任何 Azure SQL 数据库中没有的默认值吗？

塞巴斯蒂安