Testing for Not NULL 2

[dndaughtery]

I need to write a query to insert from one table to another but I want to avoid inserting empty rows. This is what I'm trying:

Insert into TableA
Select * from TableB where Fieldname Not Null;

But this is incorrect. How can I check for null value?

 

[vongrunt]

where FieldName IS NOT NULL.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLDenis]

Select * from TableB where Fieldname is Not Null;

Select * from TableB where Fieldname <> '';

or combined

Select * from TableB where coalesce(Fieldname,'') <> '';

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Or:

Select * from TableB where nullif(fieldName, '') is null

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLBill]

Question, you are inserting WHOLE rows, correct?

You seem to be only checking one column for a NULL, what if that column is NULL, but the rest of the row has data?

-SQLBill

Posting advice: FAQ481-4875

 

[dndaughtery]

Thanks a bunch!

 

[vongrunt]

> Select * from TableB where nullif(fieldName, '') is null

Typo... should be IS NOT NULL .

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[dndaughtery]

The way everything is set up there will never be an instance where the field will be null. If it is there is no way to check it, it's a foriegn key.