If null do this on insert into table variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

If null do this on insert into table variable

Author

Topic

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-05-06 : 17:04:28

Hey all-
I got a question with regards to a query that is inserting null values into another table. Here is my query....
INSERT @PRODUCTION_TOTALS (P_NUM, PT_DESC, PT_QTY, PT_ORDER, PT_TYPE )
SELECT P_NUM, Comp01Desc, Comp01, 1, 'COMPONENT'
FROM @PRODUCTIONMASTER
WHERE Comp01>0;

Here's my question. If PT_DESC is null I want it to enter 'No Description' instead of the null value. However I am doing this insert into a table variable and not an actual table. I tried using a default value, but of course that failed. Any other ideas?

As you can see I am trying to fix a poor data design into one that I can acutally query on for some reports. I have to bang off almost 30 queries to get the data in a decent format before I can start using it.

Here is a little peak at the data I am working with. This is how the other guy set it up....it was a big excel spreadsheet!

COMP01 INT,
COMP02 INT,
COMP03 INT,
COMP04 INT,
COMP05 INT,
COMP06 INT,
COMP07 INT,
COMP08 INT,
COMP09 INT,
COMP10 INT,
FIRSTPASS01 INT,
FIRSTPASS02 INT,
FIRSTPASS03 INT,
FIRSTPASS04 INT,
FIRSTPASS05 INT,
FIRSTPASS06 INT,
FIRSTPASS07 INT,
FIRSTPASS08 INT,
FIRSTPASS09 INT,
FIRSTPASS10 INT,
SCRAP01 INT,
SCRAP02 INT,
SCRAP03 INT,
SCRAP04 INT,
SCRAP05 INT,
SCRAP06 INT,
SCRAP07 INT,
SCRAP08 INT,
SCRAP09 INT,
SCRAP10 INT,
COMP01DESC VARCHAR(25),
COMP02DESC VARCHAR(25),
COMP03DESC VARCHAR(25),
COMP04DESC VARCHAR(25),
COMP05DESC VARCHAR(25),
COMP06DESC VARCHAR(25),
COMP07DESC VARCHAR(25),
COMP08DESC VARCHAR(25),
COMP09DESC VARCHAR(25),
COMP10DESC VARCHAR(25),
FIRSTPASS01DESC VARCHAR(25),
FIRSTPASS02DESC VARCHAR(25),
FIRSTPASS03DESC VARCHAR(25),
FIRSTPASS04DESC VARCHAR(25),
FIRSTPASS05DESC VARCHAR(25),
FIRSTPASS06DESC VARCHAR(25),
FIRSTPASS07DESC VARCHAR(25),
FIRSTPASS08DESC VARCHAR(25),
FIRSTPASS09DESC VARCHAR(25),
FIRSTPASS10DESC VARCHAR(25),
SCRAP01DESC VARCHAR(25),
SCRAP02DESC VARCHAR(25),
SCRAP03DESC VARCHAR(25),
SCRAP04DESC VARCHAR(25),
SCRAP05DESC VARCHAR(25),
SCRAP06DESC VARCHAR(25),
SCRAP07DESC VARCHAR(25),
SCRAP08DESC VARCHAR(25),
SCRAP09DESC VARCHAR(25),
SCRAP10DESC VARCHAR(25)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-06 : 17:31:28

You can use CASE.

INSERT @PRODUCTION_TOTALS (P_NUM, PT_DESC, PT_QTY, PT_ORDER, PT_TYPE )
SELECT P_NUM, CASE WHEN Comp01Desc IS NULL THEN 'No Description' ELSE Comp01Desc END, Comp01, 1, 'COMPONENT'
FROM @PRODUCTIONMASTER
WHERE Comp01>0;

Tara

gwhiz
Yak Posting Veteran

78 Posts

Posted - 2004-05-06 : 17:49:12

Why not just use ISNULL?
ie:

INSERT @PRODUCTION_TOTALS (P_NUM, PT_DESC, PT_QTY, PT_ORDER, PT_TYPE )
SELECT P_NUM, ISNULL(Comp01Desc,'No Description'), Comp01, 1, 'COMPONENT'
FROM @PRODUCTIONMASTER
WHERE Comp01>0;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-06 : 17:55:20

D'oh! ISNULL is the way to go for NULLs. If you need to change anything else though, use CASE.

I should have drank less caffeine today.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources