SELECT .. INTO to replace empty string or NULL value

Question

SELECT .. INTO to replace empty string or NULL value

KJP

SSChasing Mays

Points: 638
More actions
February 17, 2016 at 10:58 am

#308641

I need a sanity check on some code I've written.
I have a large table (more than 100 million rows) that needs an update on several columns. I decided to build a new table using SELECT .. INTO rather than run an UPDATE statement.
SELECT [Column1], [Column2], CASE [Column3] WHEN '' THEN 0 WHEN NULL THEN 0 ELSE [Column3] END AS 'Column3', [Column4]
INTO [dbo].[Table_Update]
FROM [dbo].[Table];
The statement runs without error, but when I query the new table I do not see the results I expect to see.
When I query the original table, I get a count of 8,390 records in which Column3 = ''. I expected to get zero rows when I query the new table for records in which Column3 = '', but instead I get the same count as before. Yet, when I query the new table for records in which Column3 = '', I see the 0 that I inserted into Column3 of the new table.
Why do I get these results when it appears that my update succeeded?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Implicit conversion is treating your '' value as a zero and making the column a numeric column. When you query a numeric column:

WHERE Column3 = ''

is the same as

WHERE Column3 = 0

This should do the trick... In your SELECT INTO statement change:

CASE [Column3] WHEN '' THEN 0 WHEN NULL THEN 0 ELSE [Column3] END AS 'Column3'

to:

CAST(CASE [Column3] WHEN '' THEN 0 WHEN NULL THEN 0 ELSE [Column3] END AS varchar(10)) AS 'Column3'

As a side note: you can simplify this part of your query like so:

CASE WHEN [Column3] IN ('',NULL) THEN 0 ELSE [Column3] END AS 'Column3'

Edit: added little side note...

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

KJP SSChasing Mays Points: 638 More actions · Answer 2

Alan.B (2/17/2016)
Implicit conversion is treating your '' value as a zero and making the column a numeric column. When you query a numeric column:
WHERE Column3 = ''
is the same as
WHERE Column3 = 0
This should do the trick... In your SELECT INTO statement change:
CASE [Column3] WHEN '' THEN 0 WHEN NULL THEN 0 ELSE [Column3] END AS 'Column3'
to:
CAST(CASE [Column3] WHEN '' THEN 0 WHEN NULL THEN 0 ELSE [Column3] END AS varchar(10)) AS 'Column3'
As a side note: you can simplify this part of your query like so:
CASE WHEN [Column3] IN ('',NULL) THEN 0 ELSE [Column3] END AS 'Column3'
That implicit conversion is becoming one of my biggest headaches on this project. Let me try your code and get back to you. Thanks.
Edit: added little side note...

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

Did what I posted help?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

KJP SSChasing Mays Points: 638 More actions · Answer 4

Yes it did, thanks for pointing this out.

I had to experiment a little bit to figure out what was going on. Two of the columns in the original table were integer columns. What I didn't realize until now is that if I insert a space into an integer column, it is automatically converted to a zero.

For those of you following this, here's an example:

CREATE TABLE [dbo].[Test] (

[Column1] CHAR(1) NULL,

[Column2] INT NULL

);

INSERT INTO [dbo].[Test] (

[Column1],

[Column2]

)

VALUES ('A', '');

SELECT *

FROM [dbo].[Test] -- Notice what value is returned for the second column

I then checked a little further, and found out there aren't any empty strings in any of the other columns either. But to be safe, I created another table with varchar columns and inserted spaces into those columns. I then ran my SELECT .. INTO statement and it updated those values as I expected.

Thanks again for your help, this was driving me crazy this afternoon.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

No problem.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

SELECT .. INTO to replace empty string or NULL value

Cookies on SQLServerCentral