SQL-Server用NULL值替换空单元格 [英] SQL-Server replace empty cells with NULL value

问题描述

我正在使用SSIS将Excel数据移动到临时sql服务器表，再从那里移动到目标表.因此，我的临时表仅包含varchar列-我的目标表期望某些列的资金价值.在我的临时表中，原始的excel列具有一个公式，但是在临时表所代表的某些行上也保留了一个空单元格，其中空单元格也是如此.但是，当我将其中一个列转换为货币时，这些最初空白的单元格在目标列中变为 0,00 .

I am using SSIS to move excel data to a temp sql server table and from there to the target table. So my temp table consists of only varchar columns - my target table expects money values for some columns. In my temp table the original excel columns have a formula but leave an empty cell on some rows which is represented by the temp table with an empty cell as well. But when I cast one of these columns to money these originally blank cells become 0,00 in the target column.

当然不是我想要的，所以如何在其中获取 NULL 值?请记住，这些列之一可能会出现想要的0,00.

Of course that is not what I want, so how can I get NULL values in there? Keeping in mind that it is possible that a wanted 0,00 shows up in one of these columns.

我想我需要编辑临时表以将空单元格设置为NULL.我可以从SSIS包中执行此操作，还是可以使用的表有设置?

I guess I would need to edit my temp table to turn the empty cells to NULL. Can I do this from within a SSIS package or is there a setting for the table I could use?

谢谢.

推荐答案

对于现有数据，您可以编写一个简单的脚本，将数据更新为空(如果为空).

For existing data you can write a simple script that updates data to NULL where empty.

UPDATE YourTable SET Column = NULL WHERE Column = ''

对于插入，您可以使用 NULLIF 函数在为空时插入空值

For inserts you can use NULLIF function to insert nulls if empty

INSERT INTO YourTable (yourColumn)
SELECT NULLIF(sourceColum, '') FROM SourceTable

对于多列更新，您需要结合两种解决方案并编写类似以下内容的内容:

for multiple column updates you need to combine the two solutions and write something like:

UPDATE YourTable SET 
Column1 = NULLIF(Column1, '')
, Column2 = NULLIF(Column2, '') 
WHERE Column1 = '' OR Column2 = '' 

等

这将更新所有内容

这篇关于SQL-Server用NULL值替换空单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！