sql - 怎么修?消息 515,级别 16,状态 2,行 2 无法将值 NULL 插入列,列不允许空值。插入失败
问题描述
我在 SQL Server 中工作。
我正在尝试stagingdata
使用另一个 ( ) 中的列中的数据加载一个新表 ( uploadfile
)。临时表的列不允许空值。
我试过这段代码:
INSERT INTO [x].[stagingdata](DepartmentCode, CourseHours, CourseCredits, CourseDescription)
SELECT
SUBSTRING([Course (hr, crd)], 1, CHARINDEX(' ', [Course (hr, crd)])) AS DeptCode,
(SUBSTRING([Course (hr, crd)], CHARINDEX('(',[Course (hr, crd)]) + 1,
LEN(CHARINDEX(',', [Course (hr, crd)]) - CHARINDEX('(', [Course (hr, crd)]) - 1))) AS CourseHours,
(SUBSTRING([Course (hr, crd)], CHARINDEX(',',[Course (hr, crd)]) + 1,
LEN(CHARINDEX(',', [Course (hr, crd)]) - CHARINDEX(')', [Course (hr, crd)]) - 1))) AS CourseCredits,
[Description]
FROM
[x].[Uploadfile]
如何在不出现此错误的情况下将数据加载到列中?
我努力了:
insert into..
select..
from...
where columnname is not null
但它不起作用。
有什么建议么?谢谢!
我也在 MAC OS 上使用 Azure DataStudio。
解决方案
select
ISNULL(substring([Course (hr, crd)], 1, charindex(' ', [Course (hr, crd)])),'') as DeptCode,
ISNULL(SUBSTRING([Course (hr, crd)], CHARINDEX('(',[Course (hr, crd)]) + 1, len(CHARINDEX(',', [Course (hr, crd)]) - CHARINDEX('(', [Course (hr, crd)]) - 1))),'') AS CourseHours,
ISNULL(SUBSTRING([Course (hr, crd)], CHARINDEX(',',[Course (hr, crd)]) + 1, len(CHARINDEX(',', [Course (hr, crd)]) - CHARINDEX(')', [Course (hr, crd)]) - 1))),'') AS CourseCredits,
ISNULL([Description],'')
推荐阅读
- unity3d - Moving GameObject with RayCast hit position causing object to move towards Raycast start position
- javascript - Issues displaying SVG into HTML Canvas element
- angular - 如何按条件设置表单有效?
- ruby-on-rails - 打开跟踪不适用于 Rails ahoy_email gem
- sql - 数据透视表上的 t-sql 辅助数据透视表
- c# - Change the time forward
- r - uncover an integer sequence where all but one values are masked by NA's
- javascript - 如何发送 XML 作为快速响应?
- javascript - 根据时区将本地时间转换为不同的时间 - javascript
- ruby - What does 'load_utility' do in COSMOS?