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.
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), CompName2. 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.CompNameFROM TempScan T LEFT JOIN Computers C ON C.CompName = T.CompNameWHERE C.CompName IS NULL; --Insert into ComputerInfo (TSQL#2)insert into ComputerInfoselect distinct C.Id, T.IP, T.MAC, T.OSfrom TempScan as T inner join Computers as C on C.CompName = T.CompName left join ComputerInfo as CI on CI.ComputerId = C.Idwhere 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 CLEFT 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 respectfulErik |
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
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)? |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|