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 |
nicholasfarmer
Starting Member
2 Posts |
Posted - 2013-08-02 : 17:14:55
|
two tables:table #1 = timestamp, servername1(never changes), value(int)Table #2 = timestamp, servername2(never changes), value(int)What I need to do is merge the two tables into a single table based on the matching timestamp:timestamp, servername1, servername2 as headers then values under"1/1/1-1:10:1" , "10" , "10" - as an example"1/1/1-1:20:1" , "20" , "21" - as an example"1/1/1-1:30:1" , "1" , "5" - as an examplePowershell or MSSQL 2008 queries work.I have a powershell script atm that can create the table and then throw it back into the database but its very slow because of the amount of records.The table is for some performance stat collection app.Table #1 records a timestamp, the servername (which is the same over and over in the table) and the value of the performance object.Table #2 is the same collection but for a different server.The layout in the database is terrible but this is what I get to work with.Please and thank you for your time. |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-08-02 : 19:16:25
|
You can use PIVOT()function [CODE]-- TEST DATADECLARE @Table1 TABLE ([timestamp] DateTime, servername1 VARCHAR(10), value INT);INSERT INTO @Table1 VALUES ('2013-07-31 14:58:05.657', 'Server1', 10),('2013-08-01 16:28:02.107', 'Server1', 20);DECLARE @Table2 TABLE ([timestamp] DateTime, servername2 VARCHAR(10), value INT);INSERT INTO @Table2 VALUES ('2013-07-31 14:58:05.657', 'Server2', 11),('2013-08-01 16:28:02.107', 'Server2', 21);-- QUERY; WITH CTE AS(SELECT [Timestamp], value, Servername1 as ServerName FROM @Table1UNIONSELECT [Timestamp], value, Servername2 as ServerName FROM @Table2)SELECT * FROM CTEPIVOT(MAX(value) FOR ServerName IN(Server1, Server2)) as P;-- OUTPUT Timestamp Server1 Server22013-07-31 14:58:05.657 10 112013-08-01 16:28:02.107 20 21[/CODE] |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-08-02 : 19:21:46
|
To create a third table with merged data, you can do this:[CODE]DECLARE @Table1 TABLE ([timestamp] DateTime, servername1 VARCHAR(10), value INT);INSERT INTO @Table1 VALUES ('2013-07-31 14:58:05.657', 'Server1', 10),('2013-08-01 16:28:02.107', 'Server1', 20);DECLARE @Table2 TABLE ([timestamp] DateTime, servername2 VARCHAR(10), value INT);INSERT INTO @Table2 VALUES ('2013-07-31 14:58:05.657', 'Server2', 11),('2013-08-01 16:28:02.107', 'Server2', 21);DECLARE @Table3 TABLE ([timestamp] DateTime, Server1 INT, Server2 INT);; WITH CTE AS(SELECT [Timestamp], value, Servername1 as ServerName FROM @Table1UNIONSELECT [Timestamp], value, Servername2 as ServerName FROM @Table2)INSERT INTO @Table3 SELECT * FROM CTEPIVOT(MAX(value) FOR ServerName IN(Server1, Server2)) as P;SELECT * FROM @Table3-- OUTPUT Timestamp Server1 Server22013-07-31 14:58:05.657 10 112013-08-01 16:28:02.107 20 21[/CODE] |
|
|
nicholasfarmer
Starting Member
2 Posts |
Posted - 2013-08-03 : 00:00:34
|
Perfect! I was able to edit what I needed, joined about 50 tables with about 300,000 records each into one large one.Dumped that into excel for boring manager graphs and we have magic!Thank you very much!! |
|
|
|
|
|
|
|