How to convert a column data type in an Update statement

Question

How to convert a column data type in an Update statement

crazekalyan

Mr or Mrs. 500

Points: 501
More actions
January 26, 2011 at 5:42 am

#234019

UPDATE
DimABCD
SET
ConsumerTypeID = D.Consumertype_ID,
UserID = D.USER_ID
From DimABCD rc
JOIN [bidw].[dbo].[TempConsumer] D ON rc.UID = D.V_ID
here the column UserID(varchar) has empty's in Tempconsumer table. and its an int column in DimABCD table.
so can you please tell me how to use convert statement and load emptys into an int column in this query.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sriram-288748 SSCrazy Points: 2874 More actions · Answer 1

crazekalyan (1/26/2011)
UPDATE
DimABCD
SET
ConsumerTypeID = D.Consumertype_ID,
UserID = D.USER_ID
From DimABCD rc
JOIN [bidw].[dbo].[TempConsumer] D ON rc.UID = D.V_ID
here the column UserID(varchar) has empty's in Tempconsumer table. and its an int column in DimABCD table.
so can you please tell me how to use convert statement and load emptys into an int column in this query.

Just check if the below sql suits your requirement, this would insert NULL's for what ever is not a numeric value, you can replace null with what ever you want.

UPDATE DimABCD

SET

ConsumerTypeID = D.Consumertype_ID,

UserID = CASE WHEN isnumer(D.USER_ID)=1 THEN D.USER_ID ELSE null

From DimABCD rc

JOIN [bidw].[dbo].[TempConsumer] D ON rc.UID = D.V_ID

Sriram

crazekalyan Mr or Mrs. 500 Points: 501 More actions · Answer 2

Thanks sriram, but i need the emptys to be loaded into the dimabdc table for USERID column.

Sriram-288748 SSCrazy Points: 2874 More actions · Answer 3

hmmm, I dont think "hmmm" is right response, but I dont think thats possible to insert " " space in int column, if we have to use a " " then why to use int, does take my understanding with respect to dbms concepts to a toss 🙂

even if you try to insert " " ... that would go down as "0" and not a space...

looking at your table names ..I feel you are working on a datawarehouse.. not sure why you want to get empty spaces into you dw ... when you want to have a int column, if you really need it, may be go for a varchar column.

Sriram

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

You have to make a choice. What does an empty string mean as an integer? Is it NULL? Is it 0? You have to supply something because a number can't be just blank. It's NULL or a value, even if that value is zero.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning