How to create a table from a view

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

How to create a table from a view

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2011-06-01 : 10:58:07

I would like to know if I have a VIEW which is a union of several tables how I can create a table from that view?

Thank you

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-01 : 11:00:39

Select * Into ANewTable From YourView

Corey

I Has Returned!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-01 : 11:03:43

What's the point of making one table from a view that is a union of 3 tables? Is there an expected performance gain?

Jim

Everyday I learn something that somebody else already knew

goligol
Posting Yak Master

128 Posts

Posted - 2011-06-01 : 11:11:30

the reseon is I want to truncate some reocrds from the view which is the union of the three tables. Due to union some records are duplicated with the same key set but different values. I want to truncate the records with the same key set to one record with the average value. I believe I can not do this in View but maybe in table.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-01 : 12:11:46

well you should have asked that... I'm sure the view could be altered to do that.

Post some table structure and expected results

Corey

I Has Returned!!

goligol
Posting Yak Master

128 Posts

Posted - 2011-06-01 : 12:54:11

Below is my view:
some records have exactly same key sets as ODMC with different value at TM0, TMcv, TMv, and the TMf is a character(s) which I want to keep.
What I want to have is one record with the key set and the average of the values on records. for example in the first two records I want to keep one with the smae ODMC key and average on TM0, TMcv, and TMv, and keep (s) for TMf.
Is that make sense?

O D M C TM0 TMf TMcv TMv
01000US 001 1 $0000 3344658 s 3.6 144980113295053
01000US 001 1 $0000 2894251 s 4.2 147764791331658
01000US 001 3 $0000 1342104 z 1.4 3530436567759.36
01000US 001 3 $0000 1055646 z 1.7 3220582699443.24
01000US 001 5 $0000 286457 1.7 237146501133.61

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-01 : 13:12:57

select ODMC, TMf, TM0=avg(TM0), TMcv = avg(TMcv), TMv = avg(TMv)
from
(
....
union
....
union
....
) a
group by ODMC, TMf

Change your view to that or create another view replacing the derived table with the original view.
Will probably need to cast to sort out the datatypes returned.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

goligol
Posting Yak Master

128 Posts

Posted - 2011-06-01 : 13:34:35

Thank you. Is this commend only create the average for the records with the same key set of ODMC and leave the rest of records which dont have duplicated key set as is?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-01 : 13:45:21

Average(1,2,3) = 2

Average(3) = 3

Why would this be an issue?

quote:
Originally posted by goligol

Thank you. Is this commend only create the average for the records with the same key set of ODMC and leave the rest of records which dont have duplicated key set as is?

Corey

I Has Returned!!

goligol
Posting Yak Master

128 Posts

Posted - 2011-06-02 : 16:38:20

you r right:) thx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources