在 SQL Server 中创建具有空值的插入查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15917235/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create Insert query with null value in SQL Server
提问by Duk
I create insert query for Organizationtable.
我为Organization表创建插入查询。
select 'Insert into Organizations(Name, IndustryId, ContactPerson, Email, Website, LocationId, ContactNumber, Mobilenumber) values(''' +
IsNull(Nameofthecompany, 'NULL') + ''',' +
Isnull(IndustryType, 'NULL') + ',''' +
Isnull(Nameofthepersonresponsibleforrecruitment, 'NULL') + ''', ''' +
Isnull(EmailId, 'NULL') + ''', ''' +
Isnull(websiteaddress, 'NULL') + ''',' +
Isnull(Location, 'NULL') + ',' +
Isnull(PhoneNumber, 'NULL') + ',' +
Isnull(MobileNumber, 'NULL') + ')'
from Organization
Here I have the result set
这里我有结果集
Insert into Organizations(Name, IndustryId, ContactPerson, Email, Website, LocationId, ContactNumber, Mobilenumber)
values('username', industry, 'Name', 'NULL', 'NULL', place, NULL, 999999999)
I don't want the NULL value within quotes. If I remove the quotes means I get error. Please Help me find out the problem..
我不想要引号内的 NULL 值。如果我删除引号意味着我会出错。请帮我找出问题..
采纳答案by Pirion
If a value is NULL, then adding it to a string will produce a NULL. This allows us to add the quotes in the ISNULL check and just produce NULL in the true value of the check, producing the correct syntax for nulls or not nulls as necessary.
如果值为 NULL，则将其添加到字符串将产生 NULL。这允许我们在 ISNULL 检查中添加引号，并在检查的真实值中生成 NULL，根据需要为空值或非空值生成正确的语法。
select 'Insert into Organizations(Name, IndustryId, ContactPerson, Email, Website, LocationId, ContactNumber, Mobilenumber) values(' +
IsNull(''''+Nameofthecompany+'''', 'NULL') + ', ' +
Isnull(''''+IndustryType+'''', 'NULL') + ', ' +
Isnull(''''+Nameofthepersonresponsibleforrecruitment+'''', 'NULL') + ', ' +
Isnull(''''+EmailId+'''', 'NULL') + ', ' +
Isnull(''''+websiteaddress+'''', 'NULL') + ', ' +
Isnull(''''+Location+'''', 'NULL') + ', ' +
Isnull(PhoneNumber, 'NULL') + ', ' +
Isnull(MobileNumber, 'NULL') + ')'
from Organization
回答by marc_s
If you want to use NULL(as a literal - not a string) for your NULL values, then the creation of the INSERTstatement gets a lot more complicated; if the value is NULL, then you need to add the literal NULLwithouta leading and trailing '.
如果您想使用NULL（作为文字 - 而不是字符串）作为您的 NULL 值，那么INSERT语句的创建会变得更加复杂；如果值为NULL，则您需要添加NULL没有前导和尾随的文字'。
For each column where you want to do this, you'll need to use a CASEstatement - something like this:
对于要执行此操作的每一列，您需要使用一个CASE语句 - 如下所示：
select 'INSERT INTO Organizations(.....) ' +
'VALUES(' +
CASE
WHEN NameOfTheCompany IS NOT NULL
THEN '''' + NameOfTheCompany + ''', '
ELSE 'NULL, '
END +
CASE
WHEN IndustryType IS NOT NULL
THEN '''' + IndustryType + ''', '
ELSE 'NULL, '
END +
..... and so on ......
+ ')'
... and so on, for each column you need this CASEstatement ....
...等等，对于每一列你都需要这个CASE语句......
