Yet another conditional insert

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)

Yet another conditional insert

Author

Topic

poolmwv
Starting Member

12 Posts

Posted - 2008-01-23 : 15:44:23

I'm sorry to be so dumb that I can't extrapolate from the conditional insert/update, but I can't find anything quite what I'm looking for.

We recently decided to change our naming standard, so I wrote two scripts that would quickly change the name in the database:

If the Serial Number was the name, I ran this:
UPDATE ASSET
SET NAME=ASSET.SERIAL_NUMBER + "." + ASSET_TYPES.TYPE
FROM ASSET JOIN ASSET_TYPES ON ASSET.ID_TYPE = ASSET_TYPES.ID
WHERE ASSET.SERIAL_NUMBER=ASSET.NAME

This would result in something like 1234.PC

If the Termid was the name, I ran this:
UPDATE ASSET
SET NAME=ASSET.SERIAL_NUMBER + "." + ASSET_TYPES.TYPE + "." + ASSET.NAME
FROM ASSET JOIN ASSET_TYPES ON ASSET.ID_TYPE = ASSET_TYPES.ID
WHERE ASSET.SERIAL_NUMBER<>ASSET.NAME

This would result in something like 5678.PRINTER.ABCD

Now I would like to change the job that runs daily to do this. This is an abbreviated version:

INSERT INTO ASSET
(ASSET.MODEL, ASSET.MFG, ASSET.SN, ASSET.TYPE, ASSET.NAME)
SELECT AU.Model, AU.MFG, AU.Serial_Number, AU.Type, AU.Name
FROM Asset_Update as AU

I could just put AU.Serial_Number + "." + AU.Type + "." + AU.Termid instead of AU.Name, but that would leave a period at the end and they don't want that. So if AU has a termid, I would like ASSET.NAME to be:
AU.Serial_Number + "." + AU.Type + "." + AU.Termid

If AU has a null termid, I would like ASSET.NAME to be:
AU.Serial_Number + "." + AU.Type

Thank you for your assistance.

I'm not an idiot, but I play one on the net.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-23 : 16:05:43

AU.Serial_Number + '.' + AU.Type + COALESCE('.'+AU.Termid, '')

poolmwv
Starting Member

12 Posts

Posted - 2008-01-23 : 16:11:51

quote:
Originally posted by jdaman

AU.Serial_Number + '.' + AU.Type + COALESCE('.'+AU.Termid, '')

Aaargh! I should have thought of that! I had just been using the COALESCE for something else but I was trying to make it more complicated. Thank you!

I'm not an idiot, but I play one on the net.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-23 : 16:14:56

quote:
Originally posted by poolmwv

quote:
Originally posted by jdaman

AU.Serial_Number + '.' + AU.Type + COALESCE('.'+AU.Termid, '')

Aaargh! I should have thought of that! I had just been using the COALESCE for something else but I was trying to make it more complicated. Thank you!

I'm not an idiot, but I play one on the net.

It usually is more simple than we first think. Glad I could help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources