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

CodePudding user response：

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

CodePudding user response：

Here is a summary of the other answers and comments.

1. You don't need a MERGE statement because you delete all the rows from the table first - so you just need an INSERT
2. I would recommend using TRUNCATE rather than DELETE for deleting all rows - unless you have a specific need for it. Pros and Cons of Truncate over Delete
3. With your INSERT statement use the code that Aaron Bertrand provided to strip the end off your computer name.

TRUNCATE TABLE clean_tanium_server;

INSERT INTO clean_tanium_server (
    computer_id
    , computer_name
    , operating_system
    , application_name
    , application_normalized_name
    , chassis_type
    , cpu_core, cpu_processor
    , ip_address)
SELECT
    computer_id
    , LEFT(computer_name, CHARINDEX('.', computer_name   '.') - 1)
    , operating_system
    , [ci_installed_application name]
    , [ci_installed_application normalized_name]
    , chassis_type
    , cpu_core
    , cpu_processor
    , ip_address
FROM tanium_server;