?
Solved

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

Posted on 2010-04-07
11
Medium Priority
?
592 Views
Last Modified: 2012-05-09
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..
0
Comment
Question by:nomar2
  • 6
  • 4
11 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 30041158
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
 

Author Comment

by:nomar2
ID: 30045183
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 30045864
sure:


create procedure procname
as
insert into tablename(fieldname1, fieldname2, fieldname3)
select fieldname1, fieldname2, fieldname3
from viewname
go
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 

Author Comment

by:nomar2
ID: 30048965
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
 
LVL 60

Accepted Solution

by:
chapmandew earned 752 total points
ID: 30049328
create procedure procname
as
delete from tablename
insert into tablename(fieldname1, fieldname2, fieldname3)
select fieldname1, fieldname2, fieldname3
from viewname
go
0
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 30056667
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
 

Author Comment

by:nomar2
ID: 30065155
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 30068350
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 30068579
>>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
 

Author Comment

by:nomar2
ID: 30135008
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 30135046
good idea.  :)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

594 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question