join 3 views in one view

Question

Hi Expert,

i wanted to join three views in one using temp table i.e.

Create view test1 as

select col1,col2 into #temp1 from view1

select col1,col2 into #temp2 from view2

select col1,col2 into #temp3 from view3

select col1,col2 from #temp1 a join #temp2 b on a.col1=b.col1

how can i do this

Answer 1

Hi @Shambhu Rai

Try something like this:

CREATE PROC SP_TEST1
AS
SELECT col1, col2 INTO #temp1 FROM view1;
SELECT col1, col2 INTO #temp2 FROM view2;
SELECT col1, col2 INTO #temp3 FROM view3;
SELECT col1, col2 INTO #temp4
FROM (SELECT col1,col2 FROM #temp1 a JOIN #temp2 b ON a.col1 = b.col1) c
JOIN #temp3 d ON c.col1 = d.col1;
GO
EXEC SP_TEST1;
GO
CREATE VIEW view_test
AS
SELECT * FROM #temp4
GO

Answer 2

Hi @Shambhu Rai , although this is an old thread, but talks about your exact use-case: Thanks

SQL How to join these three views into one?

https://stackoverflow.com/questions/19469652/sql-how-to-join-these-three-views-into-one

Answer 3

Hi @Shambhu Rai

You don't need to use temp table, just use view1/view2/view3 as dataset.

Create view test1 as
select a.col1,b.col2 from view1 a join view2 b on a.col1=b.col1

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

how can i do this

In that way with a #temp table you can't, no way, any kind of DML statements are forbidden in a view.

Use a UNION ALL operator to combine the result of the 3 views, see Set Operators - UNION (Transact-SQL)

Answer 5

Hi @Shambhu Rai

A view consists of a single SELECT statement. You cannot create or drop tables in a view.

If you need to do something like that, then try using stored procedure.