How to add a Conditional Default Value on a SQL Table Field?

[RovirozM]

Hi Guys

Maybe an easy question, but I don't know how to do it....

I have a SQL Table that is filled from an External Application. What I like to do is the next:

I Have a field from this Table that now is filled with "NA" in every new record, this "NA" comes from the Default value on the SQL Table on this field (Because the application doesn't fill this field)

How can I tell this field that every record instead of NA put another value, but with a conditional, I mean where other key field is equal to something? Some fields will be with "NA" and others with "MyNewValue" (Depending of the condition)..

Is this one a constraint or something?

Thanks!

 

[kjv1611]

I think the easiest will be to use an INSERT or UPDATE statement, whichever you're using, I'm going to assume INSERT for the example, with a UNION query pulling the 2 different queried conditions... I'll put it in code in the next post so you can see what I mean.

 

[kjv1611]

Here goes...

[TT]INSERT INTO	SandboxTable
SELECT	 e.NormalField1 ,e.NormalField2 ,e.NormalField3
	      ,Values(NULL) [ConditionalField]
FROM	 ExternalApplication e WITH (NOLOCK)
WHERE	e.ConditionalField = "N/A"
UNION
SELECT   e.NormalField1 ,e.NormalField2 ,e.NormalField3
		,e.ConditionalField
FROM	ExternalApplication e WITH (NOLOCK)
WHERE   e.ConditionalField <> "N/A"
[/TT]

That's just hypothetical code, of course. You'd have to plug in the real values, make changes as appropriate. I'm assuming in the ineample that if you don't get "N/A" as a result for said field, that you want the actual value.

Also, this may not be the best performance way. You may need to change the <> portion of the second conditional statement to boost performance, but the UNION will be a big help in total overall performance anyway.

 

[RovirozM]

Thank you kjv1611, but where can I Put this?

A Place on the SQL table?

Thanks!

 

[kjv1611]

If it's a one-time run, you simply create a new Query Editor Window, and put the code there (edit to fit your tables, fields, etc), and run from there. You don't put the SQL code directly into the tables.

But actually, if you thought that was a possibility, I'd be sure to run this by someone else in the same company with some SQL know-how to be sure you've got everything correct in your environment. You don't want to accidentally run any code that would make incorrect changes to existing data.

Also, since it's coming from an external source, I cannot say of 100% assurety that it will work correctly as is. You may have to involve some other code or changes to this code to convert the data from the other system to the SQL Server table. That would be application specific, but it could include simply exporting from the other application to text, then importing that into a SQL Table - probably a Sandbox table (would be my guess).