CODEWITHSUNDEEP
sqlsql-serverinsertrecord
סטנלי גרונן
סטנלי גרונן

Reputation: 2997

How to insert into a table new records based on info from another table?

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).

Upvotes: 1

Views: 609

Answers (7)

Shiju Shaji
Shiju Shaji

Reputation: 1730

Merge is another way.. 

 MERGE <[The second table]> t
USING [The first table: ] s
  ON t.DeviceIPAddr    = s.DeviceIPAddr   
WHEN NOT MATCHED 
  INSERT (DeviceIPAddr, Status, Timestamp)
  VALUES (s.DeviceIPAddr,0, getutcdate())
;

Upvotes: 2

mmuzahid
mmuzahid

Reputation: 2270

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;

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28890

insert into secondtable
select deiviceipaddr,0,getdate()
from isttable t1
where not exists(select 1 from secondtable t2 where t1.ipaddress=t2.ipaddress)

---one more way

insert into secondtable
select deiviceipaddr,status from firsttable
except
select deiviceipaddr,status from secondtable

Upvotes: 1

mohan111
mohan111

Reputation: 8865

DECLARE @Table1 TABLE 
    (DeviceID int, DeviceSwVersion int, DeviceIPAddr varchar(11))
;

INSERT INTO @Table1
    (DeviceID, DeviceSwVersion, DeviceIPAddr)
VALUES
    (1, 802, '172.26.20.1'),
    (115, 800, '172.26.18.1'),
    (1234, 264, '172.26.18.3'),
    (4717, 264, '172.26.19.2'),
    (14157, 264, '172.26.19.1')
; 

DECLARE @Table2 TABLE 
    (DeviceIPAddr varchar(11), Status int, TimeStamp varchar(19))
;

INSERT INTO @Table2
    (DeviceIPAddr, Status, TimeStamp)
VALUES
    ('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')

; USING NOT EXISTS 

Select TT.DeviceIPAddr,0 Status,getdate() TimeStamp from @Table1 TT 
        where  NOT EXISTS 
                (select 1 from @Table2 T where T.DeviceIPAddr = TT.DeviceIPAddr)

OR

USING EXCEPT 

Select TT.DeviceIPAddr,0 AS Status, getdate() AS  TimeStamp from @Table1 TT
EXCEPT 
select T.DeviceIPAddr,0 AS Status, getdate() AS  TimeStamp from @Table2 T

Upvotes: 1

sagi
sagi

Reputation: 40471

So you can use NOT EXISTS() for that, like this:

INSERT INTO SecondTable 
(SELECT t.DeviceIpAddr , 0 as status,getdate()
 FROM FirstTable t
 WHERE NOT EXISTS(select 1 from SecondTable s where t.DeviceIpAddr  = s.DeviceIpAddr )

Or, you can use NOT IN() like this:

 INSERT INTO SecondTable 
(SELECT t.DeviceIpAddr , 0 as status,getdate()
 FROM FirstTable t
 WHERE t.DeviceIpAddr  NOT IN(select distinct s.DeviceipAddr from SecondTable)

Upvotes: 1

bmsqldev
bmsqldev

Reputation: 2735

Insert into table2
(DeviceIPAddr, Status, TimeStamp)

SELECT t1.deiviceipaddr, 0, GETDATE()
FROM Table1
Left Outer Join Table2
ON (t1.DeviceIPAddr = t2. DeviceIPAddr)
WHERE t2. DeviceIPAddr IS NULL

Upvotes: 1

Dan O&#39;Leary
Dan O&#39;Leary

Reputation: 2822

 INSERT Table2
 SELECT   DeviceIPAddr, 0, getDate()
 FROM    Table1
 WHERE   DeviceIpAddr NOT IN (SELECT DeviceIpAddr FROM Table2)

Upvotes: 1

Related Questions