Remove row in table A if doesn't exist in table B and put row in table C

Transact-SQL
1

Hello,

I have temp table created as such:

CREATE TABLE #Raw_NIC_Imported_TMP(
        [MACAddress] [varchar](50) NOT NULL,
        [Host] [varchar](255) NOT NULL,
        [IPAddress] [varchar](50) NOT NULL,
        [IPSubnet] [varchar](50) NULL,
        [DefaultIPGateway] [varchar](50) NULL,
        [DNSServer1] [varchar](50) NULL,
        [DNSServer2] [varchar](50) NULL,
        [WINSPrimary] [varchar](50) NULL,
        [WINSSecondary] [varchar](50) NULL,
        [InterfaceName] [varchar](255) NULL,
     CONSTRAINT [PK_IPAddress1] PRIMARY KEY CLUSTERED ( [IPAddress] ASC )
    );

The contents of the file varies depending on which computers get audited. So one truth is that if a system is audited, all of its network adapters are captured along with all of their assigned IP Addresses.

Example Data:

MACAddress    Host    IPAddress    IPSubnet    DefaultIPGateway    DNSServer1    DNSServer2    WINSPrimary    WINSSecondary    InterfaceName
00:1F:C9:4E:B5:01    SVR1.my.domain    10.191.32.11    255.255.255.0    10.191.32.254    10.10.2.20    10.10.2.22    NULL    NULL    Broadcom NetXtreme 57xx Gigabit Controller
00:50:56:D0:00:01    SVR1.my.domain    192.168.43.1    255.255.255.0    NULL    NULL    NULL    NULL    NULL    VMware Virtual Ethernet Adapter for VMnet1
00:50:56:F0:00:08    SVR1.my.domain    192.168.61.1    255.255.255.0    NULL    NULL    NULL    NULL    NULL    VMware Virtual Ethernet Adapter for VMnet8
00:50:56:C0:00:08    SVR3.my.domain    192.168.66.1    255.255.255.0    NULL    NULL    NULL    NULL    NULL    VMware Virtual Ethernet Adapter for VMnet8
78:2B:AB:1A:0F:1E    SVR3.my.domain    10.191.32.1    255.255.255.0    10.191.32.254    10.10.2.20    10.10.2.22    NULL    NULL    Broadcom BCM5716C NetXtreme II GigE (NDIS VBD Client) #11
00:50:56:E0:00:01    SVR3.my.domain    192.168.160.1    255.255.255.0    NULL    NULL    NULL    NULL    NULL    VMware Virtual Ethernet Adapter for VMnet1

I have already set up a MERGE process that inserts or updates the production table below that that handles new additions or updates to existing entries.

CREATE TABLE tbl_Raw_NIC_Imported(
        [MACAddress] [varchar](50) NOT NULL,
        [Host] [varchar](255) NOT NULL,
        [IPAddress] [varchar](50) NOT NULL,
        [IPSubnet] [varchar](50) NULL,
        [DefaultIPGateway] [varchar](50) NULL,
        [DNSServer1] [varchar](50) NULL,
        [DNSServer2] [varchar](50) NULL,
        [WINSPrimary] [varchar](50) NULL,
        [WINSSecondary] [varchar](50) NULL,
        [InterfaceName] [varchar](255) NULL,
     CONSTRAINT [PK_IPAddress1] PRIMARY KEY CLUSTERED ( [IPAddress] ASC ))
    );

Example Data:

MACAddress    Host    IPAddress    IPSubnet    DefaultIPGateway    DNSServer1    DNSServer2    WINSPrimary    WINSSecondary    InterfaceName
00:1F:C9:4E:B5:01    SVR1.my.domain    10.191.32.11    255.255.255.0    10.191.32.254    10.10.2.20    10.10.2.22    NULL    NULL    Broadcom NetXtreme 57xx Gigabit Controller
00:22:19:8B:65:F0    SVR1.my.domain    10.191.32.10    255.255.255.0    10.191.32.254    10.10.2.20    10.10.2.22    NULL    NULL    Broadcom BCM5708C NetXtreme II GigE (NDIS VBD Client)
00:50:56:D0:00:01    SVR1.my.domain    192.168.43.1    255.255.255.0    NULL    NULL    NULL    NULL    NULL    VMware Virtual Ethernet Adapter for VMnet1
00:50:56:F0:00:08    SVR1.my.domain    192.168.61.1    255.255.255.0    NULL    NULL    NULL    NULL    NULL    VMware Virtual Ethernet Adapter for VMnet8
00:50:56:C0:00:08    SVR3.my.domain    192.168.66.1    255.255.255.0    NULL    NULL    NULL    NULL    NULL    VMware Virtual Ethernet Adapter for VMnet8
78:2B:AB:1A:0F:1E    SVR3.my.domain    10.191.32.1    255.255.255.0    10.191.32.254    10.10.2.20    10.10.2.22    NULL    NULL    Broadcom BCM5716C NetXtreme II GigE (NDIS VBD Client) #11
00:50:56:E0:00:01    SVR3.my.domain    192.168.160.1    255.255.255.0    NULL    NULL    NULL    NULL    NULL    VMware Virtual Ethernet Adapter for VMnet1

I have two questions as follows:

1. I would like to reverse the process in a manner of speaking. The production table is to be a true picture of the current state so I don't want adapters configurations that are no longer in use. Would someone be willing to explain or show how I might do a comparison between the PROD tbl and the TMP tbl to identify that the TMP tbl lacks the following entry and then remove it from the PROD tbl? During the merge, I match ON (TargetTable.IPAddress = SourceTable.IPAddress) AND (TargetTable.Host = SourceTable.Host).

00:22:19:8B:65:F0    SVR1.my.domain    10.191.32.10    255.255.255.0    10.191.32.254    10.10.2.20    10.10.2.22   NULL    NULL    Broadcom BCM5708C NetXtreme II GigE (NDIS VBD Client)

2. Within the answer to number one, how could it be composed so that it writes the lines that are removed from the PROD tbl to the HISTORY tbl?

CREATE TABLE tbl_Raw_NIC_History(
        [MACAddress] [varchar](50) NOT NULL,
        [Host] [varchar](255) NOT NULL,
        [IPAddress] [varchar](50) NOT NULL,
        [IPSubnet] [varchar](50) NULL,
        [DefaultIPGateway] [varchar](50) NULL,
        [DNSServer1] [varchar](50) NULL,
        [DNSServer2] [varchar](50) NULL,
        [WINSPrimary] [varchar](50) NULL,
        [WINSSecondary] [varchar](50) NULL,
        [InterfaceName] [varchar](255) NULL,
        [DateRemoved] [DATETIME] NOT NULL DEFAULT (GETDATE()));

Any assistance would be greatly appreciated. I have been struggling with trying to find the correct search terms to locate an example on the Internet.

Thanks in advance,

Casey

2

I've put a simple example, which I'm hoping matches your expectations and you should be able to apply to your scenario

DECLARE @TestA TABLE
(
	ID INT NOT NULL,
	RandomVal CHAR(1) NOT NULL
);

DECLARE @TestATemp TABLE
(
	ID INT NOT NULL,
	RandomVal CHAR(1) NOT NULL
);

DECLARE @TestAHistory TABLE
(
	MergeAction VARCHAR(10) NOT NULL,
	ID INT NOT NULL,
	RandomVal CHAR(1) NOT NULL
);

INSERT INTO @TestA(ID,RandomVal)
VALUES (1,'A'),(2,'B'),(3,'C');

INSERT INTO @TestATemp(ID,RandomVal)
VALUES (1,'A'),(2,'B');

SELECT * FROM @TestA;
SELECT * FROM @TestATemp;
SELECT * FROM @TestAHistory;

	MERGE @TestA AS Target
	USING @TestATemp AS Source
	ON (
			TARGET.ID = SOURCE.ID
		)
	WHEN NOT MATCHED BY SOURCE THEN DELETE
	OUTPUT $action, deleted.ID, deleted.RandomVal into @TestAHistory(MergeAction,ID,RandomVal);
	
SELECT * FROM @TestA;
SELECT * FROM @TestATemp;
SELECT * FROM @TestAHistory;
3

Dohsan,

Thanks for the assistance. This will take me a bit to adapt and get back to you.

Thanks again,

Casey

4

You can also look at the EXCEPT and INTERSECT commands. for comparing tables.
Here is one site: https://www.simple-talk.com/sql/performance/the-except-and-intersect-operators-in-sql-server/

5

Thank you both for you prompt assistance.

Dohsan, it worked like a charm. Again, much thanks for the excellent example.

6

Glad you were able to solve your problem Casey. It's certainly a lot easier to help when an OP provides DDL with examples and detail like you have.

7

Dohsan,

If I might trouble you a moment more, here is what I came up with based off of your example:

-- Purge from PROD table any NICs not in import for a system that is in the file
MERGE tbl_Raw_NIC_Imported WITH (HOLDLOCK) AS TargetTable
USING #Raw_NIC_Imported_TMP AS SourceTable
ON (TargetTable.IPAddress = SourceTable.IPAddress) AND (TargetTable.Host = SourceTable.Host)
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT deleted.MACAddress,
     deleted.Host,
     deleted.IPAddress,
     deleted.IPSubnet,
     deleted.DefaultIPGateway,
     deleted.DNSServer1 ,
     deleted.DNSServer2,
     deleted.WINSPrimary,
     deleted.WINSSecondary,
     deleted.InterfaceName,
     Getdate ()
into tbl_Raw_NIC_History(MACAddress,Host,IPAddress,IPSubnet,DefaultIPGateway,DNSServer1,DNSServer2,WINSPrimary,WINSSecondary,InterfaceName,DateRemoved);

All. looked good when I was manually testing with one file as I was seeing the same NICs over and over. I put the stored procedure into an SSIS Foreach Loop Container and fed it several files. When it was done processing, it became apparent that only the last file processed was properly in the prod table. All previous files were moved to the history table.

Did I miss something from your explanation. My intent was to only move systems that were in the PROD table to the HISTORY table if the following conditions were true:

1. The HOST has to exist in the TEMP table due to the uncertainty of all systems ever being audited at the same time.

2. The NIC in the PROD table isn't listed in the TMP table by MATCH on (HOST and IPAddress). This would be to remove adapters that some administrator had removed from a host.

Thanks,

Casey

8

With the MERGE statement the following

WHEN NOT MATCHED BY SOURCE THEN DELETE

Is essentially saying when there is a row in the TARGET table that is not in the SOURCE table when matched on provided criteria, i.e.

ON (TargetTable.IPAddress = SourceTable.IPAddress) AND (TargetTable.Host = SourceTable.Host)

Delete it from the TARGET table and then move it to the History table using the OUPUT clause

What you're likely finding is that looping through a set of files, which I assume all contain different data, will cause a lot of rows to be moved out of the TARGET table on each iteration.

Are you iterating through each file and running the MERGE against it each time? What is the result if you iterate through each file and insert the results into the same temp table (so one table containing all the different files data) and then run the MERGE statement once at the end once you've passed through all the files?

9

Dohsan,

The Foreach Container causes each file to be operated on individually. Based off what I am seeing happen and your comments, I would assume that it wouldn't matter if I queued all of the changes in the files and processed them at once. I would still be in the same position where real data would get flushed to the history table since not all systems are likely to always get audited each time. They could be offline for maintenance, etc...

Is it possible to build a data-set based off of a join between the two tables to only have the merge statement act upon the hosts that are common between the production table and the temp table and then only remove only those records from the production table that aren't in the temp table?

Respectfully,

Casey

10

I think I see what you're getting at, I've modified my original code to MERGE only on the target table when the ID exists in the temp table:

DECLARE @TestA TABLE
(
	ID INT NOT NULL,
	RandomVal CHAR(1) NOT NULL
);

DECLARE @TestATemp TABLE
(
	ID INT NOT NULL,
	RandomVal CHAR(1) NOT NULL
);

DECLARE @TestAHistory TABLE
(
	MergeAction VARCHAR(10) NOT NULL,
	ID INT NOT NULL,
	RandomVal CHAR(1) NOT NULL
);

INSERT INTO @TestA(ID,RandomVal)
VALUES (1,'A'),(2,'B'),(3,'C');

INSERT INTO @TestATemp(ID,RandomVal)
VALUES (1,'Z'),(2,'B');

SELECT * FROM @TestA;
SELECT * FROM @TestATemp;
SELECT * FROM @TestAHistory;

	WITH TargetBASE
	AS
	(
		SELECT	TA.ID,
				TA.RandomVal
		FROM	@TestA AS TA
		WHERE	EXISTS(SELECT 1 FROM @TestATemp AS TAT WHERE TA.ID = TAT.ID)
	)
	MERGE TargetBASE AS Target
	USING @TestATemp AS Source
	ON (
			TARGET.ID = SOURCE.ID
			AND TARGET.RandomVal = SOURCE.RandomVal
		)
	WHEN NOT MATCHED BY SOURCE THEN DELETE
	OUTPUT $action, deleted.ID, deleted.RandomVal into @TestAHistory(MergeAction,ID,RandomVal);
	
SELECT * FROM @TestA;
SELECT * FROM @TestATemp;
SELECT * FROM @TestAHistory;

You'll notice that originally ID 3 was the row that was deleted from the TestA table and inserted into the history table. Now it ignores ID 3 as it isnt common between the TestA and TestATemp table, this time as ID 1 has a new value, it will be moved.

Does this get you more to where you want? May be worth provided some example table of what would exist in your production table, the temp table and what you would expect to still be there/archived when the process is finished.

11

Dohsan,

Sir, your right on the money with that version. What a relief. I can't thank you enough.

    WITH TargetBASE
AS
(
    SELECT    TA.Host,
            TA.IPAddress,
            TA.MACAddress,
            TA.IPSubnet,
            TA.DefaultIPGateway,
            TA.DNSServer1 ,
            TA.DNSServer2,
            TA.WINSPrimary,
            TA.WINSSecondary,
            TA.InterfaceName
    FROM    tbl_Raw_NIC_Imported AS TA
    WHERE    EXISTS(SELECT 1 FROM #Raw_NIC_Imported_TMP AS TAT WHERE TA.Host = TAT.Host)
)
MERGE TargetBASE AS Target
USING #Raw_NIC_Imported_TMP AS Source
ON (
        TARGET.Host = SOURCE.Host
        AND TARGET.IPAddress = SOURCE.IPAddress
    )
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT deleted.MACAddress,
        deleted.Host,
        deleted.IPAddress,
        deleted.IPSubnet,
        deleted.DefaultIPGateway,
        deleted.DNSServer1 ,
        deleted.DNSServer2,
        deleted.WINSPrimary,
        deleted.WINSSecondary,
        deleted.InterfaceName,
        Getdate () 
into tbl_Raw_NIC_History(MACAddress,Host,IPAddress,IPSubnet,DefaultIPGateway,DNSServer1,DNSServer2,WINSPrimary,WINSSecondary,InterfaceName,DateRemoved);