How to populate a temp table with a view using a stored procedure .. SQL Server

I have a view created in Sql Server 2008 that returns a set of rows..I have a table created that I want to hold the results from the view because I have to manipulate the data in there without having to change the original data..I also wanted to run a trigger on this temp table..

Can this be done from a stored procedure or do I have to write the code out in VB.Net which is the language I am using..

Basically take the results from a view and populate a table..

Any help or insight would be much appreciated..
nomar2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
you can't create triggers on temp tables, but to create a temp table in a proc from a view:

create procedure procname
as
select *
into #temptable
from viewname
go
0
nomar2Author Commented:
That doesn't seem to be working for me...

if I have a table already created to match the data coming back..is there any way to populate the table that is already created rather creating a temp table on the fly...

0
chapmandewCommented:
sure:


create procedure procname
as
insert into tablename(fieldname1, fieldname2, fieldname3)
select fieldname1, fieldname2, fieldname3
from viewname
go
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

nomar2Author Commented:
Andrew,

  That actually seems to be working and I have a Insert trigger on the table as well which seems to be doing what it should after the rows are inserted..
This is a table that is going to be used quite alot... right now I have this as the SP....

ALTER procedure [dbo].[PopulateTable]
as
insert into tReportTable([FIELDS]..etc)
select ([Fields]..etc)
from vBrkDwnRpt4


1 last Question..


Is there anyway in this same SP that I could blow out all the rows of this table tReportTable before inserting the new data back in from the view..the view dta will be constantly changing..

your help has been great and much appreciated

0
chapmandewCommented:
create procedure procname
as
delete from tablename
insert into tablename(fieldname1, fieldname2, fieldname3)
select fieldname1, fieldname2, fieldname3
from viewname
go
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProjectChampionCommented:
I'd use truncate table instead of delete from.
It'll work much faster and will have a much smaller footprint as it doesn't log the deletes. You wouldn't want to engage transaction log specially if the table is going to have large number of rows.
Further more in the job/stored proc which will be responsible for populating the table, I would drop all indexes (if any) before the insert and create them again after insert is done, for two obvious reason:
1. Insert would be much faster without any indexes;
2. Deleting and then inserting large number of rows is guaranteed to result in badly fragmented indexes, recreating indexes after the insert ensures you'll have nice and tidy indexes to begin with, after every data refresh.
0
nomar2Author Commented:
The table will have a large number of rows as time goes on..so that is good suggestion with

TRUNCATE TABLE table_name

I just created a table by right clicking on the Table Folder in the Db and choosing "New Table" without the use of indexes..

I will try Truncate Table in the morning to see if it works
0
chapmandewCommented:
I didn't include the truncate statement for a purpose.  Truncate requires the user who calls the proc to have either db_owner perms, or a user that owns the table.  You can use EXECUTE AS, but the idea behind it is overkill.

Also, if by some happenstance another use ever calls the procedure, you're broken.  That happens that way w/ the DELETE statement too...unless you use criteria.
0
chapmandewCommented:
>>It'll work much faster and will have a much smaller footprint as it doesn't log the deletes

Actually, it does log them...just not fully.

http://www.sqlskills.com/BLOGS/PAUL/post/Lock-logging-and-fast-recovery.aspx
0
nomar2Author Commented:
TRUNCATE TABLE table_Name  works..but I will replace that with

DELETE FROM TABLENAME

.this table is being used for a report that only 1 person will ever use..so another user won't be calling it
0
chapmandewCommented:
good idea.  :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.