I have a table that has a bunch of different servers most of them are listed as servername.ad.edu. I want to remove everything after the first dot. so it just shows as servername in my table. The code I was provided for the population of the table was not written by me but I did add the LEFT statement to see if I could remove it during population. Here is my code…
DELETE FROM clean_tanium_server; MERGE clean_tanium_server AS Target USING tanium_server AS Source ON Source.computer_id = Target.computer_id AND Source.[ci_installed_application name] = Target.application_name WHEN NOT MATCHED BY Target THEN INSERT (computer_id, computer_name, operating_system, application_name, application_normalized_name, chassis_type, cpu_core, cpu_processor, ip_address) VALUES (Source.computer_id, Source.computer_name, Source.operating_system, Source.[ci_installed_application name], Source.[ci_installed_application normalized_name], Source.chassis_type, Source.cpu_core, Source.cpu_processor, Source.ip_address) WHEN MATCHED THEN UPDATE SET *Target.computer_name = LEFT(Source.computer_name, CHARINDEX('.', Source.computer_name) - 1),* Target.operating_system = Source.operating_system, Target.application_normalized_name = Source.[ci_installed_application normalized_name] WHEN NOT MATCHED BY Source THEN DELETE;
So I can’t figure out why when I populate the tables they aren’t omitting the .ad.edu portion. Any help is greatly appreciated. Thank you.
How it looks currently:
computer_name servername1.ad.edu servername2.ad.edu servername3.us.edu
How I want it to look:
computer_name servername1 servername2 servername3
You’re only applying the LEFT changes when entering the WHEN MATCHED THEN UPDATE SET block, but as pointed out in the comments, you’re emptying the table first, so you’ll never match and will only execute the WHEN NOT MATCHED BY Target THEN block which doesn’t have your LEFT modifications