February 17, 2016 at 10:58 am
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?
February 17, 2016 at 11:23 am
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...
-- Itzik Ben-Gan 2001
February 17, 2016 at 11:27 am
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 = 0This 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...
February 17, 2016 at 1:58 pm
Did what I posted help?
-- Itzik Ben-Gan 2001
February 17, 2016 at 2:14 pm
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.
February 17, 2016 at 2:33 pm
No problem. 😎
-- Itzik Ben-Gan 2001
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply