INSERT INTO with SELECT and dealing with nulls

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

INSERT INTO with SELECT and dealing with nulls

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-12-27 : 15:16:34

I am writing a SQL script that will import data from a SQL 2000 database into a newly created sql 2008r2 database. To do this I am simply using INSERT INTO statements as the column naming in the new database is different but some of the columns I am inserting the data into are NOT NULL columns while the source data can be null. Is it possible to craft the INSERT INTO SELECT statement to grab the value from the column and insert it but if the column is NULL to insert a default value?

Sudo Code to help explain kind of what I am hoping to be able to do:

INSET INTO dbo.NewTable (COL1)
SELECT IF (COL1 IS NOT NULL) COL1Value ELSE 'MyDefaultValue'

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

cgraus
Starting Member

12 Posts

Posted - 2013-12-27 : 15:26:19

Yes, this is easy to do.

SELECT IF (COL1 IS NOT NULL) COL1Value ELSE 'MyDefaultValue'

becomes

Select Coalesce(Col1, 'MyDefaultValue')

this actually expands out to exactly the code you wrote, internally, but is more readable and easier to churn out. There is also an ISNULL function which can be used, and is very similar. For a list of the differences, read my article on select statements, at http://www.codeproject.com/Articles/700317/SQL-Wizardry-Episode-One-Joins

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-12-27 : 15:54:02

Thanks, it has been so long since I have worked in SQL I completely forgot about the COALESCE function.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-28 : 03:29:48

quote:
Originally posted by Eagle_f90

Thanks, it has been so long since I have worked in SQL I completely forgot about the COALESCE function.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

and just for your information if its sql 2012
you can use this


INSERT INTO dbo.NewTable (COL1)
SELECT IIF (COL1 IS NULL,'MyDefaultValue',COL1Value )
FROM...

But internaly all of these (COALESCE,IIF etc) is evaluated as a CASE..WHEN expression

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Kristen
Test

22859 Posts

Posted - 2013-12-28 : 09:19:11

quote:
Originally posted by cgraus

There is also an ISNULL function which can be used, and is very similar.

A significant difference, which may be critical in this situation, is that ISNULL handles implicit data conversion badly, so if the datatype of the first and second parameters is different the results may be unexpected.

COALESCE() has better behaviour in this regard, so I think would be a better choice.

@Eagle_f90 : If the datatype of the two parameters is identical then I expect it doesn't matter. FWIW we always use COALESCE as it is standards-compliant (not that we conform!), allows more than 2 parameters, and ISNULL is a stupid name for a replacement function, as it sounds more like a Logical function :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources