SQL-Server用NULL值替换空单元格

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.

我正在使用SSIS将excel数据移动到临时sql服务器表并从那里移动到目标表。所以我的临时表只包含varchar列 - 我的目标表需要某些列的货币值。在我的临时表中，原始的excel列具有公式，但在某些行上留下一个空单元格，这些行由临时表表示，并且还有一个空单元格。但是，当我将这些列中的一列投入金钱时，这些原始空白单元格在目标列中变为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值呢？请记住，在这些列之一中可能会出现想要的0,00。

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?

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

thank you.

谢谢。

1 个解决方案

#1

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

对于现有数据，您可以编写一个简单的脚本，将数据更新为NULL，其中为空。

UPDATE YourTable SET Column = NULL WHERE Column = ''

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

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

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

Edit: 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 = ''

etc

等等

That will update all

这将更新所有

#1