I am using SQL Server 2012 on Windows7. I currently have 2 tables.
The first table:
DeviceID DeviceSwVersion DeviceIPAddr
1 802 172.26.20.1
115 800 172.26.18.1
1234 264 172.26.18.3
4717 264 172.26.19.2 // <- new
14157 264 172.26.19.1 // <- new
The second table:
DeviceIPAddr Status TimeStamp (default=getdate())
172.26.20.1 1 2016-02-09 10:25:01
172.26.18.1 1 2016-02-09 10:30:12
172.26.18.3 1 2016-02-09 10:33:08
What I need is a SQL query to insert into 2nd table new rows corresponding to the new DeviceIP that are now present in the first table. Only the new DeviceIPs that are not already there in the 2nd table.
So, finally the 2nd table should look like this:
DeviceIPAddr Status TimeStamp // default = getdate()
172.26.20.1 1 2016-02-09 10:25:01
172.26.18.1 1 2016-02-09 10:30:12
172.26.18.3 1 2016-02-09 10:33:08
172.26.19.2 0 2016-02-10 09:53:00
172.26.19.1 0 2016-02-10 09:53:01
Remark: Status column is 0 for new inserted rows and TimeStamp is the current date-time (default value filled automatically by getdate() function).
You could write a AFTER INSERT TRIGGER something like bellow:
CREATE TRIGGER trigger_insert_table_1
ON table_1
AFTER INSERT AS
BEGIN
INSERT INTO table_2
( DeviceIPAddr,
Status,
TimeStamp)
VALUES
( NEW.DeviceIPAddr,
0,
getdate() );
END;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments