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
 split a table

Author  Topic 

eirikr_1
Starting Member

27 Posts

Posted - 2013-02-07 : 12:20:57
I need some help with a query to split a table
master table
Id ComputerName Ip Vuln ScanDate
1 computerA 1.1.1 iav1 1/1/13
2 computerA 1.1.1 iav2 1/1/13
3 computerB 2.2.2 iav3 2/1/13

into to
computerInfo table
Id ComputerName Ip
1 computerA 1.1.1
2 computerB 2.2.2

vulnerabilities table
Id ComputerId Vuln ScanDate
1 1 iav1 1/1/13
2 1 iav2 1/1/13
3 2 iav3 2/1/13

thank you

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-07 : 16:43:03
[code]-- first table
SELECT DISTINCT IDENTITY(INT,1,1) as ID, ComputerName INTO ComputerInfoTable FROM MasterTable;

-- second table
SELECT
a.Id,b.Id AS ComputerId,a.Vuln,a.ScanDate
INTO VulnerabilitiesTable
FROM
MasterTable a
INNER JOIN ComputerInfoTable b ON a.ComputerName = b.computername;
[/code]
Go to Top of Page

eirikr_1
Starting Member

27 Posts

Posted - 2013-02-10 : 01:21:34
Thank you James
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-10 : 06:40:01
You are very welcome - glad to help.
Go to Top of Page
   

- Advertisement -