CODEWITHSUNDEEP
sql-servert-sqlinsertcompare
TheEggSample
TheEggSample

Reputation: 343

How to Insert Row Only If Any Column Differs From Matching Row In Another Table

Essentially I am pulling in a list of computers on our network. I would like to compare the info in my temp table #general for a specific computer name XXXX-XXXX-XXXXX in my ADScanGeneral table. Essentially I want to compare the columns of #general's computer entry with the latest entry in ADScanGeneral, and if they differ anywhere, then enter the row in #general into ADScanGeneral as a new entry. Otherwise if they're the same, then just update the ADScanGeneral.EntryDate column for that computer.

My problem is that I can't think of a way to compare the columns of a single entry in #general and ADScanGeneral without doing a long while loop. Is this possible in a SQL statement?

Examples of my table below:

"#general"

Name    Status  lastLogon   LastLogonDate   lastLogonTimestamp  ResolvedIP  Manufacturer    Model   Architecture    TotalPhysicalMemory LastLoggedOnUser    WakeUpType  OperatingSystem OperatingSystemVersion  OperatingSystemArchitecture SystemDrive SerialNumber    SMBIOSBIOSVersion   BIOSVersion CPUName CPUCaption  MaxClockSpeed   LastClockSpeed  NumberOfProcessors  NumberOfCores   NumberOfThreads
XXXX-XXXX-10362 1   2018-02-01 06:37:18 2018-01-27 22:37:03 2018-01-27 22:37:03 10.1.19.7   Dell Inc.   OptiPlex 390    x64-based PC    8481869824  XXXXX\amanda.creathbaum 6   Microsoft Windows 10 Pro    10.1.7601   64-bit  C:  G6WLTR1 A01 DELL   - 6222004    Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz Intel64 Family 6 Model 42 Stepping 7    3101    1581    1   4   4
XXXX-XXXX-10947 0   2018-02-01 06:29:57 2018-02-01 02:09:54 2018-02-01 02:09:54 10.1.19.4                                                                               
XXXX-XXXX-01738 0   2018-02-01 17:58:37 2017-11-13 17:58:37 2017-11-13 17:58:37

"ADScanGeneral"

Name    Status  lastLogon   LastLogonDate   lastLogonTimestamp  ResolvedIP  Manufacturer    Model   Architecture    TotalPhysicalMemory LastLoggedOnUser    WakeUpType  OperatingSystem OperatingSystemVersion  OperatingSystemArchitecture SystemDrive SerialNumber    SMBIOSBIOSVersion   BIOSVersion CPUName CPUCaption  MaxClockSpeed   LastClockSpeed  NumberOfProcessors  NumberOfCores   NumberOfThreads    EntryDate
XXXX-XXXX-10362 1   2018-02-01 06:37:18 2018-01-27 22:37:03 2018-01-27 22:37:03 10.1.19.7   Dell Inc.   OptiPlex 390    x64-based PC    8481869824  XXXXX\amanda.creathbaum 6   Microsoft Windows 7 Professional    6.1.7601    64-bit  C:  G6WLTR1 A01 DELL   - 6222004    Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz Intel64 Family 6 Model 42 Stepping 7    3101    1581    1   4   4    2018-02-01 06:37:18
XXXX-XXXX-10947 0   2018-02-01 06:29:57 2018-02-01 02:09:54 2018-02-01 02:09:54 10.1.19.4                                                                               2018-02-01 06:37:18
XXXX-XXXX-01738 0   2017-11-13 17:58:37 2017-11-13 17:58:37 2017-11-13 17:58:37                                                                                 2018-02-01 06:37:18

So for the first entry, the operating system has changed to Windows 10, so I will want to insert this entire entry as a new entry into ADScanGeneral. The second entry, nothing has changed, so I want to update the entry date in ADScanGeneral. The third entry has a more up to date lastLogon so I would like to add this as a new entry into ADScanGeneral as well.

Does that make sense? If you need clarification, please ask and I'll do my best to help. Thanks everyone who helps!

JoeC suggested checking out the merge command, but it appears as though it only matches the 2 fields you specify in the ON condition, while I need it to match the entire row. So I'm unsure if I'm just using the command incorrectly or if it won't work for me. Here's the merge command I came up with:

MERGE   ADScanGeneral   AS TARGET
USING   #General        AS SOURCE
ON      (TARGET.[Name] = SOURCE.[Name])
WHEN MATCHED THEN
        UPDATE SET EntryDate = getdate()
WHEN NOT MATCHED THEN
        INSERT  (
                    [Name], 
                    [Status], 
                    LastLogon, 
                    LastLogonDate, 
                    LastLogonTimestamp,
                    ResolvedIP, 
                    Manufacturer, 
                    Model, 
                    Architecture, 
                    TotalPhysicalMemory, 
                    LastLoggedOnUser,
                    WakeUpType,
                    OperatingSystem,
                    OperatingSystemVersion,
                    OperatingSystemArchitecture,
                    SystemDirectory,
                    SerialNumber,
                    SMBIOSVersion,
                    BIOSVersion,
                    CPUName,
                    CPUCaption,
                    MaxClockSpeed,
                    NumberOfProcessors,
                    NumberOfThreads,
                    NumberOfCores
                )
        VALUES  (
                    SOURCE.[Name], 
                    SOURCE.[Status], 
                    SOURCE.LastLogon, 
                    SOURCE.LastLogonDate, 
                    SOURCE.LastLogonTimestamp,
                    SOURCE.ResolvedIP, 
                    SOURCE.Manufacturer, 
                    SOURCE.Model, 
                    SOURCE.Architecture, 
                    SOURCE.TotalPhysicalMemory, 
                    SOURCE.LastLoggedOnUser,
                    SOURCE.WakeUpType,
                    SOURCE.OperatingSystem,
                    SOURCE.OperatingSystemVersion,
                    SOURCE.OperatingSystemArchitecture,
                    SOURCE.SystemDirectory,
                    SOURCE.SerialNumber,
                    SOURCE.SMBIOSVersion,
                    SOURCE.BIOSVersion,
                    SOURCE.CPUName,
                    SOURCE.CPUCaption,
                    SOURCE.MaxClockSpeed,
                    SOURCE.NumberOfProcessors,
                    SOURCE.NumberOfCores,
                    SOURCE.NumberOfThreads
                );

Upvotes: 1

Views: 442

Answers (1)

gomory-chvatal
gomory-chvatal

Reputation: 332

One way to accomplish this is to use the output clause to keep track of the names that have been inserted and perform updates in a second query after filtering to remove the names that were inserted.

There are probably better ways to check for changes but the below code should work.

declare @inserted_names table ([Name] varchar(255));

-- insert into ADScanGeneral any records from #general with changes, or any
-- name that is not present in ADScanGeneral
-- the names of the inserted record are stored to a table variable
insert into ADScanGeneral
output inserted.[Name] into @inserted_names ([Name])
select
    c.*, GetDate() as EntryDate
from
    ADScanGeneral as a
inner join
    (
        select
            a.[Name], max(a.EntryDate) as MaxEntryDate
        from
            ADScanGeneral as a
        group by
            a.[Name]
    ) as b
    on
        a.[Name] = b.[Name]
        and
        a.EntryDate = b.MaxEntryDate
right join
    #general as c
    on
        (a.[Name] = c.[Name] or a.[Name] is null)
        and
        (
            (a.[Status] <> c.[Status] or (a.[Status] is null and c.[Status] is not null) or (a.[Status] is not null and c.[Status] is null))
            or (a.[lastLogon] <> c.[lastLogon] or (a.[lastLogon] is null and c.[lastLogon] is not null) or (a.[lastLogon] is not null and c.[lastLogon] is null))
            or (a.[LastLogonDate] <> c.[LastLogonDate] or (a.[LastLogonDate] is null and c.[LastLogonDate] is not null) or (a.[LastLogonDate] is not null and c.[LastLogonDate] is null))
            or (a.[lastLogonTimestamp] <> c.[lastLogonTimestamp] or (a.[lastLogonTimestamp] is null and c.[lastLogonTimestamp] is not null) or (a.[lastLogonTimestamp] is not null and c.[lastLogonTimestamp] is null))
            or (a.[ResolvedIP] <> c.[ResolvedIP] or (a.[ResolvedIP] is null and c.[ResolvedIP] is not null) or (a.[ResolvedIP] is not null and c.[ResolvedIP] is null))
            or (a.[Manufacturer] <> c.[Manufacturer] or (a.[Manufacturer] is null and c.[Manufacturer] is not null) or (a.[Manufacturer] is not null and c.[Manufacturer] is null))
            or (a.[Model] <> c.[Model] or (a.[Model] is null and c.[Model] is not null) or (a.[Model] is not null and c.[Model] is null))
            or (a.[Architecture] <> c.[Architecture] or (a.[Architecture] is null and c.[Architecture] is not null) or (a.[Architecture] is not null and c.[Architecture] is null))
            (a.[TotalPhysicalMemory] <> c.[TotalPhysicalMemory] or (a.[TotalPhysicalMemory] is null and c.[TotalPhysicalMemory] is not null) or (a.[TotalPhysicalMemory] is not null and c.[TotalPhysicalMemory] is null))
            or (a.[LastLoggedOnUser] <> c.[LastLoggedOnUser] or (a.[LastLoggedOnUser] is null and c.[LastLoggedOnUser] is not null) or (a.[LastLoggedOnUser] is not null and c.[LastLoggedOnUser] is null))
            or (a.[WakeUpType] <> c.[WakeUpType] or (a.[WakeUpType] is null and c.[WakeUpType] is not null) or (a.[WakeUpType] is not null and c.[WakeUpType] is null))
            or (a.[OperatingSystem] <> c.[OperatingSystem] or (a.[OperatingSystem] is null and c.[OperatingSystem] is not null) or (a.[OperatingSystem] is not null and c.[OperatingSystem] is null))
            or (a.[OperatingSystemVersion] <> c.[OperatingSystemVersion] or (a.[OperatingSystemVersion] is null and c.[OperatingSystemVersion] is not null) or (a.[OperatingSystemVersion] is not null and c.[OperatingSystemVersion] is null))
            or (a.[OperatingSystemArchitecture] <> c.[OperatingSystemArchitecture] or (a.[OperatingSystemArchitecture] is null and c.[OperatingSystemArchitecture] is not null) or (a.[OperatingSystemArchitecture] is not null and c.[OperatingSystemArchitecture] is null))
            or (a.[SystemDrive] <> c.[SystemDrive] or (a.[SystemDrive] is null and c.[SystemDrive] is not null) or (a.[SystemDrive] is not null and c.[SystemDrive] is null))
            or (a.[SerialNumber] <> c.[SerialNumber] or (a.[SerialNumber] is null and c.[SerialNumber] is not null) or (a.[SerialNumber] is not null and c.[SerialNumber] is null))
            or (a.[SMBIOSBIOSVersion] <> c.[SMBIOSBIOSVersion] or (a.[SMBIOSBIOSVersion] is null and c.[SMBIOSBIOSVersion] is not null) or (a.[SMBIOSBIOSVersion] is not null and c.[SMBIOSBIOSVersion] is null))
            or (a.[BIOSVersion] <> c.[BIOSVersion] or (a.[BIOSVersion] is null and c.[BIOSVersion] is not null) or (a.[BIOSVersion] is not null and c.[BIOSVersion] is null))
            or (a.[CPUName] <> c.[CPUName] or (a.[CPUName] is null and c.[CPUName] is not null) or (a.[CPUName] is not null and c.[CPUName] is null))
            or (a.[CPUCaption] <> c.[CPUCaption] or (a.[CPUCaption] is null and c.[CPUCaption] is not null) or (a.[CPUCaption] is not null and c.[CPUCaption] is null))
            or (a.[MaxClockSpeed] <> c.[MaxClockSpeed] or (a.[MaxClockSpeed] is null and c.[MaxClockSpeed] is not null) or (a.[MaxClockSpeed] is not null and c.[MaxClockSpeed] is null))
            or (a.[LastClockSpeed] <> c.[LastClockSpeed] or (a.[LastClockSpeed] is null and c.[LastClockSpeed] is not null) or (a.[LastClockSpeed] is not null and c.[LastClockSpeed] is null))
            or (a.[NumberOfProcessors] <> c.[NumberOfProcessors] or (a.[NumberOfProcessors] is null and c.[NumberOfProcessors] is not null) or (a.[NumberOfProcessors] is not null and c.[NumberOfProcessors] is null))
            or (a.[NumberOfCores] <> c.[NumberOfCores] or (a.[NumberOfCores] is null and c.[NumberOfCores] is not null) or (a.[NumberOfCores] is not null and c.[NumberOfCores] is null))
            or (a.[NumberOfThreads] <> c.[NumberOfThreads] or (a.[NumberOfThreads] is null and c.[NumberOfThreads] is not null) or (a.[NumberOfThreads] is not null and c.[NumberOfThreads] is null))
        )



-- udate the records in ADScanGeneral for any names that were not inserted
-- during the previous step
update a
set
    a.EntryDate = getdate()
from
    ADScanGeneral as a
inner join
    (
        select
            a.[Name], max(a.EntryDate) as MaxEntryDate
        from
            ADScanGeneral as a
        group by
            a.[Name]
    ) as b
    on
        a.[Name] = b.[Name]
        and
        a.EntryDate = b.MaxEntryDate
inner join
    #general as c
    on
        a.[Name] = c.[Name]
left join
    @inserted_names as d
    on
        a.[Name] = d.[Name]
where
    d.[Name] is null

Upvotes: 1

Related Questions