Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to prevent duplicated rows to be inserted

Author  Topic 

eirikr_1
Starting Member

27 Posts

Posted - 2013-02-27 : 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-27 : 04:16:58
which columns represent unique business key for vulnerabilities table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

eirikr_1
Starting Member

27 Posts

Posted - 2013-02-27 : 10:43:18
no unique key is set on Vulnerabilities because IAV can be any number or N/A.
Go to Top of Page

eirikr_1
Starting Member

27 Posts

Posted - 2013-02-27 : 12:29:15
is it a bad idea to combine columns to make a unique key uc_xxx UNIQUE (CompId,IAV,IAVName,AuditId)?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-27 : 22:44:53
its not. Unless you can identify a unique set of columns you wont be able to check for presence of a record and interpret whether its an existing one with modification or a new entry altogether

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -