Merge two tables/column into one

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

SQL Server 2005 Forums

Transact-SQL (2005)

Merge two tables/column into one

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-10-21 : 20:24:58

I have the following two queries and I need the result set from each to be merged based on time. The two tables have no real relationship, events are recorded in one table and operator actions are recorded in the second table. The only loose relationship is time.

Select EventDetails.EventID, EventDetails.EventStartTime, EventDetails.Description
From EventDetails
Order BY EventStartTime

Result:
EventID EventStartTime Description
9 2006-01-01 01:00:00.000 Desc EID9
15 2007-01-01 02:00:00.000 h
4 2008-01-01 03:00:00.000 Description EID4

Select OperatorAction.OperatorActionID, OperatorAction.ActionTime, OperatorAction.ActionDescription
From OperatorAction
Order BY ActionTime

Result:
OperatorActionID ActionTime ActionDescription
8 2006-01-01 01:30:00.000 Test Action 4 EID10
9 2007-01-01 02:30:00.000 Alix description
2 2008-01-01 03:30:00.000 Test Action 1 EID4

So I need the outcome to be:
Create table #t([ID] int, [DateTime] DateTime, [Description] nvarchar(max))
Insert into #t

Result:
ID DateTime Description
9 2006-01-01 01:00:00.000 Desc EID9
8 2006-01-01 01:30:00.000 Test Action 4 EID10
15 2007-01-01 02:00:00.000 h
9 2007-01-01 02:30:00.000 Alix description
4 2008-01-01 03:00:00.000 Description EID4
2 2008-01-01 03:30:00.000 Test Action 1 EID4

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-10-21 : 21:09:33

My question has changed as the code right at the bottom here seems to work. How would one do the same task if the number of columns in each table do not match?? Example directly below. Thanks

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[VCP_Search_MDS_AD] Script Date: 10/22/2008 10:04:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[VCP_Search_MDS_AD]

As

Create table #t([ID] int, [Time] DateTime, [LocalTime] DateTime, [Description] nvarchar (max), [Comments] nvarchar(max))
Insert into #t
Select EventDetails.EventID, EventDetails.EventStartTime, EventDetails.LocalStartTime, EventDetails.Description, EventComments.Comments
From EventDetails
Left Join EventComments ON EventDetails.EventID = EventComments.EventID
Union all
(Select OperatorAction.OperatorActionID, OperatorAction.ActionTime, OperatorAction.ActionDescription From OperatorAction)

Select *
From #t
Order BY Time

Return

Original question, this works.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[VCP_Search_MDS_AD] Script Date: 10/22/2008 10:04:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[VCP_Search_MDS_AD]

As

Create table #t([ID] int, [Time] DateTime, [Description] nvarchar (max))
Insert into #t
Select EventID, EventDetails.EventStartTime, EventDetails.Description
From EventDetails
Union all
(Select OperatorAction.OperatorActionID, OperatorAction.ActionTime, OperatorAction.ActionDescription From OperatorAction)

Select *
From #t
Order BY Time

Return

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-10-21 : 23:02:07

This works but surely there must be a better way?

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[VCP_Search_MDS_AD] Script Date: 10/22/2008 10:04:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[VCP_Search_MDS_AD]

As

Create table #t([ID] int, [LocalTime] DateTime, [Description] nvarchar (max), [SiteTime] DateTime, [Comments] nvarchar(max))
Insert into #t
Select EventDetails.EventID, EventDetails.LocalStartTime, EventDetails.Description, EventDetails.EventStartTime, EventComments.Comments
From EventDetails
Left Join EventComments ON EventDetails.EventID = EventComments.EventID
Union all
(Select OperatorAction.OperatorActionID, OperatorAction.ActionTime, OperatorAction.ActionDescription, REPLACE(OperatorAction.ActionTime, '', NULL) , REPLACE(OperatorAction.ActionDescription, '', NULL) From OperatorAction)

Select *
From #t
Order BY LocalTime

Return

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 00:33:49

No need of REPLACE() just hardcode NULL values as other columns to make number of columns equal on both queries seperated by union all

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-10-22 : 02:06:25

OK, like this, and it works. Is this an efficent way of merging?

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[VCP_Search_MDS_AD] Script Date: 10/22/2008 16:09:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[VCP_Search_MDS_AD]

As

Create table #t([Event or Action ID] int, [LocalTime] DateTime, [Event or Action Description] nvarchar (max), [Site Time] DateTime, [Event or Action Type] nvarchar(50), [Event Priority] int, [Event Comments] nvarchar(max), [Event Comment Time] DateTime, [Site Name] nvarchar (50), [Camera or User Name] nvarchar(50), [Camera Number] int)
Insert into #t

Select EventDetails.EventID, EventDetails.LocalStartTime, EventDetails.Description, EventDetails.EventStartTime, EventDetails.EventType, EventDetails.Priority, EventComments.Comments, EventComments.CommentedTime, SiteDetails.SiteName, EventDetails.SourceType, EventDetails.CameraNumber
From EventDetails INNER JOIN
SessionDetails ON EventDetails.SessionID = SessionDetails.SessionID INNER JOIN
SiteDetails ON EventDetails.SiteID = SiteDetails.SiteID LEFT JOIN
OperatorAction ON SessionDetails.SessionID = OperatorAction.SessionID LEFT JOIN
EventComments ON EventDetails.EventID = EventComments.EventID
GROUP BY EventDetails.EventID, EventDetails.LocalStartTime, EventDetails.Description, EventDetails.EventStartTime, EventDetails.EventType, EventDetails.Priority, EventComments.Comments, EventComments.CommentedTime, SiteDetails.SiteName, EventDetails.SourceType, EventDetails.CameraNumber

Union All

(Select OperatorAction.OperatorActionID, OperatorAction.ActionTime, OperatorAction.ActionDescription, NULL, OperatorAction.ActionType, NULL, NULL, NULL, SiteDetails.SiteName, UserDetails.LoginName, NULL
From OperatorAction Inner Join
UserDetails on OperatorAction.UserID = UserDetails.UserID INNER JOIN
SessionDetails ON OperatorAction.SessionID = SessionDetails.SessionID INNER JOIN
EventDetails ON SessionDetails.SessionID = EventDetails.SessionID INNER JOIN
SiteDetails ON EventDetails.SiteID = SiteDetails.SiteID
GROUP BY OperatorAction.OperatorActionID, OperatorAction.ActionTime, OperatorAction.ActionDescription, OperatorAction.ActionType, SiteDetails.SiteName, UserDetails.LoginName
)

Select *
From #t
Order BY LocalTime Desc

Return

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources