|
eirikr_1
Starting Member
26 Posts |
 Posted - 02/27/2013 : 02:17:50
|
Three tables are defined as:
1. Computers table has Id(PK), CompName
2. ComputerInfo table has Id(PK), CompId (FK to Computers Id), IP,OS, MAC,…
3. Vulnerabilities table has Id(PK), CompId (FK to Computer Id), IAV, IAVName, AuditDate,…
Here is my TSQL to split TempScan table into 3 different tables: Computers, ComputerInfo, Vulnerabilities.
--Insert into Computers (TSQL#1)
INSERT INTO Computers(CompName)
SELECT distinct T.CompName
FROM
TempScan T
LEFT JOIN Computers C
ON C.CompName = T.CompName
WHERE
C.CompName IS NULL;
--Insert into ComputerInfo (TSQL#2)
insert into ComputerInfo
select distinct C.Id, T.IP, T.MAC, T.OS
from TempScan as T
inner join Computers as C on C.CompName = T.CompName
left join ComputerInfo as CI on CI.ComputerId = C.Id
where CI.ComputerId is NULL
--Insert into Vulnerabilities (TSQL#3)
INSERT INTO Vulnerabilities (ComputerId,IAV,Name,AuditDate,…)
SELECT C.Id AS ComputerId, T.IAV, T.Name, T.AuditDate,…
FROM Computers as C
LEFT JOIN TempScan as T ON T.CompName = C.CompName
• When these TSQL statements are called more than twice to split data from TempScan to 3 tables, how can I prevent duplicated data to be inserted into Vulnerabilities table.
• With TSQL#2, a duplicated data won’t be inserted into ComputerInfo, however, in case someone changes some of the computer’s IP, or OS, or anything from TempScan table, and run TSQL#2, how can I delete old rows and insert new rows, or update rows that have data changed.
Please help.
~Very respectful
Erik
|
|
how to prevent duplicated rows to be inserted
