41779

SQL-Server replace empty cells with NULL value

Question:

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 <strong>empty cell</strong> 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 <strong>0,00</strong> in the target column.

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

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?

thank you.

Answer1:

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

UPDATE YourTable SET Column = NULL WHERE Column = ''

For inserts you can use <a href="http://msdn.microsoft.com/en-us/library/ms177562.aspx" rel="nofollow">NULLIF</a> function to insert nulls if empty

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

Recommend

  • What is the difference and issues between these two clojure functions?
  • Request map direct me to Login page in Grails
  • Fastest way to save/load data.table
  • Int to char conversion rule in C when int is outside the range of char
  • R matching more than 2 conditions and return the response value
  • finding symmetric difference/unique elements in multiple arrays in javascript
  • Casting double to integer when is it undefined behaviour
  • Type mismatch: cannot convert from ListFragment to Fragment
  • Keep Sql Connection open for iterating many requests? Or close each step?
  • How to delete column from range if cell contains specific value in VBA/Excel
  • Grails eager fetch doesn't retrieve all data
  • Why is it ambiguous to call overloaded ambig(long) and ambig(unsigned long) with an integer literal?
  • C# Remove URL from String
  • Binary Tree Traversal Sum Of Each Depth
  • What exactly is the height of modalPresentationStyle - FormSheet on iPad?
  • replacing while loop with list comprehension
  • CSS Grid, position absolute an element in a css grid item: IMPOSSIBLE
  • Entity Framework ObjectContext: Concurrency
  • RavenDB indexing errors
  • calculating number of bytes of each row in an image
  • Excel distinct count with conditions
  • Multiple Left Join LINQ-to-entities
  • Want to understand iframe breakout code
  • Suqueries in select clause with JPA
  • What is the correct way to synchronize a shared, static object in Java?
  • Julia: How to give multiple workers access to functions that are 'include(…)' into a modul
  • Getting the scrolling offset when storing coordinates
  • Default parameter as generic type
  • DIV instruction jumping to random location?
  • Center align outputs in ipython notebook
  • custom UITableViewCell with image for highlighting
  • Jackson Parser: ignore deserializing for type mismatch
  • How to use remove-erase idiom for removing empty vectors in a vector?
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Why is an OPTIONS request sent to the server?
  • Sending data from AppleScript to FileMaker records
  • vba code to select only visible cells in specific column except heading
  • Do I've to free mysql result after storing it?
  • Transpose CSV data with awk (pivot transformation)
  • Sorting a 2D array using the second column C++