执行SQL更新语句存JSON到nvarchar(max)列报标识符过长错误求助

SQL Server 中双引号默认用于标识数据库对象名（列名、表名等），而非包裹字符串字面量。你用双引号包裹JSON串时，数据库会将整段JSON识别为一个对象标识符，而SQL规定标识符最大长度为128字符，因此触发该报错，和字段本身的存储容量限制无关。

优先使用标准JSON格式（键与字符串值用双引号包裹），再用单引号包裹整个JSON串作为字符串字面量写入SQL语句，修改后的正确语句如下：

UPDATE Table_Json
SET JSON = '{"follow_up_timestamp": "2021-09-21 22:16:36", "id_fu": "", "redcap_survey_identifier": "", "nps": "76", "improve": "test", "dowell": "test", "follow_up_complete": "Complete", "australian_hospital_patient_experience_question_se_timestamp": "[not completed]", "more_qu": "Yes", "views": "Always", "needs": "Always", "unmet_need": "", "cared": "", "involvement": "", "informed": "Sometimes", "team_communication": "Sometimes", "pain": "", "confident": "", "harm_distress": "", "harm_discussed": "", "quality": "Average", "comments": "hhj", "australian_hospital_patient_experience_question_se_complete": "Incomplete"}'
-- 请自行补充WHERE条件指定更新的目标行，否则会修改全表所有行的该字段值

如果你需要保留原JSON内的单引号写法，需要将JSON内部所有单引号替换为两个单引号完成SQL转义，再用单引号包裹整段内容：

UPDATE Table_Json
SET JSON = '{''follow_up_timestamp'': ''2021-09-21 22:16:36'', ''id_fu'': '''',''redcap_survey_identifier'': '''', ''nps'': ''76'', ''improve'': ''test'', ''dowell'': ''test'', ''follow_up_complete'': ''Complete'', ''australian_hospital_patient_experience_question_se_timestamp'': ''[not completed]'', ''more_qu'': ''Yes'', ''views'': ''Always'', ''needs'': ''Always'', ''unmet_need'': '''', ''cared'': '''', ''involvement'': '''', ''informed'': ''Sometimes'', ''team_communication'': ''Sometimes'', ''pain'': '''', ''confident'': '''', ''harm_distress'': '''', ''harm_discussed'': '''', ''quality'': ''Average'', ''comments'': ''hhj'', ''australian_hospital_patient_experience_question_se_complete'': ''Incomplete''}'

• 标准JSON格式可直接适配SQL Server内置的JSON_VALUE、OPENJSON等JSON处理函数，后续使用更便捷，更推荐使用。
• 所有UPDATE操作执行前请确认WHERE条件，避免误改全表数据。

内容的提问来源于stack exchange，提问作者SammyG