SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to prevent duplicated rows to be inserted
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

eirikr_1
Starting Member

26 Posts

Posted - 02/27/2013 :  02:17:50  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/27/2013 :  04:16:58  Show Profile  Reply with Quote
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

26 Posts

Posted - 02/27/2013 :  10:43:18  Show Profile  Reply with Quote
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

26 Posts

Posted - 02/27/2013 :  12:29:15  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/27/2013 :  22:44:53  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000