Insert Into from 4 select queries

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

Insert Into from 4 select queries

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-08 : 11:17:54

Hi,

I have 4 tables which I am extracting 2 distinct values;

Select Distinct UniId, PID from dbo.Event1 
Select Distinct UniId, PID from dbo.Event2 
Select Distinct UniId, PID from dbo.Event3 
Select Distinct UniId, PID from dbo.Event4

Then, I want to select Distinct between these 4 tables (Event1, Event2, Event3 and Event4)

Then insert the distinct records of the 4 tables to the final table - dbo.EventLookup .

Any help please..

Thank you

gvmk27
Starting Member

44 Posts

Posted - 2014-10-08 : 11:21:10

Try this.

Create a table variable

DECLARE @tblDistinctEvent table(
UnitID int,
PID int)

INSERT INTO @tblDistinctEvent
Select Distinct UniId, PID from dbo.Event1

INSERT INTO @tblDistinctEvent
Select Distinct UniId, PID from dbo.Event2

INSERT INTO @tblDistinctEvent
Select Distinct UniId, PID from dbo.Event3

INSERT INTO @tblDistinctEvent
Select Distinct UniId, PID from dbo.Event4

Select Distinct UniId, PID from @tblDistinctEvent

quote:
Originally posted by dr223

Hi,

I have 4 tables which I am extracting 2 distinct values;

Select Distinct UniId, PID from dbo.Event1
Select Distinct UniId, PID from dbo.Event2
Select Distinct UniId, PID from dbo.Event3
Select Distinct UniId, PID from dbo.Event4

Then, I want to select Distinct between these 4 tables (Event1, Event2, Event3 and Event4)

Then insert the distinct records of the 4 tables to the final table - dbo.EventLookup .

Any help please..

Thank you

SSSQL2008R2
Starting Member

3 Posts

Posted - 2014-10-08 : 11:32:12

Hi,

You should be able to use CTE for this.

;WITH DistinctValues
AS(
Select Distinct UniId UniID, PID PID from dbo.Event1
union
Select Distinct UniId UniID, PID PID from dbo.Event2
union
Select Distinct UniId UniID, PID PID from dbo.Event3
union
Select Distinct UniId UniID, PID PID from dbo.Event4
) SELECT DISTINCT UniId, PID FROM DistinctValues

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources