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
 Quick merge table query help please.

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 example


Powershell 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 DATA
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);
-- QUERY

; WITH CTE AS
(SELECT [Timestamp], value, Servername1 as ServerName FROM @Table1
UNION
SELECT [Timestamp], value, Servername2 as ServerName FROM @Table2)
SELECT * FROM CTE
PIVOT(MAX(value) FOR ServerName IN(Server1, Server2)) as P;



-- OUTPUT
Timestamp Server1 Server2
2013-07-31 14:58:05.657 10 11
2013-08-01 16:28:02.107 20 21
[/CODE]
Go to Top of Page

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 @Table1
UNION
SELECT [Timestamp], value, Servername2 as ServerName FROM @Table2)
INSERT INTO @Table3 SELECT * FROM CTE
PIVOT(MAX(value) FOR ServerName IN(Server1, Server2)) as P;


SELECT * FROM @Table3


-- OUTPUT
Timestamp Server1 Server2
2013-07-31 14:58:05.657 10 11
2013-08-01 16:28:02.107 20 21
[/CODE]
Go to Top of Page

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!!
Go to Top of Page
   

- Advertisement -