Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lampis
Starting Member
10 Posts |
 Posted - 2011-09-27 : 07:47:07
|
| Hello,When I import data from a .csv-file, MS SQL Server 2008 gives the value "0" if there is no value at all for a particular date. I want to have NULL as a value instead in these cases.How would I go about if I wanted to change the value "0" to "NULL" in a column in one of my tables?/lampis |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2011-09-27 : 08:45:20
|
update [your table] set [your column] = NULL where [your column] = '0'use at your own risk  http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-27 : 08:56:00
|
| Are you sure there are no genuine ZERO values in the CSV file? (in addition to "blank" values)(If this is a DATE / DATETIME column you should be getting 01-Jan-1900, rather than "0" as such. Maybe the CSV import can be configured to treat blank-strings as NULL, rather than as "" - because if you store an empty STRING to a DATE or NUMERIC field you will indeed get a "zero" value because there will be an implicit conversion from STRING to DATE / Numeric value) |
|
|
|
lampis
Starting Member
10 Posts |
Posted - 2011-09-27 : 09:29:23
|
quote:
Originally posted by DonAtWork
update [your table] set [your column] = NULL where [your column] = '0'use at your own risk http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp
Thnx. Yes it can be risky. |
|
|
|
lampis
Starting Member
10 Posts |
Posted - 2011-09-27 : 09:31:39
|
quote:
Originally posted by Kristen
Are you sure there are no genuine ZERO values in the CSV file? (in addition to "blank" values)(If this is a DATE / DATETIME column you should be getting 01-Jan-1900, rather than "0" as such. Maybe the CSV import can be configured to treat blank-strings as NULL, rather than as "" - because if you store an empty STRING to a DATE or NUMERIC field you will indeed get a "zero" value because there will be an implicit conversion from STRING to DATE / Numeric value)
Well at least now it's now genuine zero values, don't know if there will be in the future though. Yes, I would like to be able to treat blank-strings as NULL, but not sure on how to do it. I use the "Import and Export Data"-tool for MS SQL Server 2008 and import by flat file. Any idea on how to treat ""-values as NULL? That would be the best solution for me. |
|
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
Posted - 2011-09-27 : 09:35:21
|
| just my 1 cent.Converting any value to NULL never makes sense. I think having NULLS in your tables is a bad thing to have. As you cant compare NULL, NULLS have many drawbacks read about this online.------------------------------------------------The answer is always no till than you don't ask. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-27 : 10:36:27
|
| "I think having NULLS in your tables is a bad thing to have"Sorry, but that is complete tosh!NULL signifies an unknown value.For a date column, let say "Date of Birth", when a CSV file has a blank string in in date-of-birth column what value are you going to store? If you just let it import as "blank string" then the date of birth will be set to 01-Jan-1900 - very dangerous."you cant compare NULL"But that's the point, surely? If a value is unknown how can it be equal to another record with an unknown value?Are a Father and his Son, who both have their Birth Date set to NULL, "equal" ?Once you know their birth dates you can enter that data, until then NULL is the correct value.Arbitrary values like the 99th day of the 99th month in the 99th year are an equally bad method of portraying "unknown". |
|
|
|
|
|
|
|
|
Change value fron zero to NULL