我有以下选择查询,该查询可以按预期运行(它的目的是从另一个表中的字符串构建单词列表):
SELECT UPPER(LTRIM(RTRIM(allWords.words)))
FROM
(
SELECT section.Cols.value('.', 'varchar(250)') words
FROM @xml.nodes('/c') section(Cols)
) AS allWords
WHERE
LTRIM(RTRIM(words)) <> ''
AND dbo.RegExIsMatch('.*[\W\d].*',LTRIM(RTRIM(words)),1) <> 1
AND LEN(LTRIM(RTRIM(words))) > 3
GROUP BY words
但是,当我添加INTO
声明时,它失败了:
IF OBJECT_ID('usr.nameList') IS NOT NULL DROP TABLE usr.nameList;
SELECT UPPER(LTRIM(RTRIM(allWords.words)))
INTO usr.nameList
FROM
(
SELECT section.Cols.value('.', 'varchar(250)') words
FROM @xml.nodes('/c') section(Cols)
) AS allWords
WHERE
LTRIM(RTRIM(words)) <> ''
AND dbo.RegExIsMatch('.*[\W\d].*',LTRIM(RTRIM(words)),1) <> 1
AND LEN(LTRIM(RTRIM(words))) > 3
GROUP BY words
输出为:
对象或列名称丢失或为空。对于SELECT INTO语句,请验证每个列都有一个名称。对于其他语句,请查找空别名。不允许将别名定义为“”或[]。将别名更改为有效名称。
如果SELECT
查询有效,为什么将数据插入新表的这种简单修改会失败?
您在错误消息中缺少了什么?您需要为列命名:
SELECT UPPER(LTRIM(RTRIM(allWords.words))) as words
INTO usr.nameList
FROM
(
SELECT section.Cols.value('.', 'varchar(250)') words
FROM @xml.nodes('/c') section(Cols)
) AS allWords
WHERE
LTRIM(RTRIM(words)) <> ''
AND dbo.RegExIsMatch('.*[\W\d].*',LTRIM(RTRIM(words)),1) <> 1
AND LEN(LTRIM(RTRIM(words))) > 3
GROUP BY words;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句