Creating Temporary Table From a View

Hi,
I would like to create a temporary table is a stored procedure which
contains all the same set of columns as one of my view in the
database. Is there anyway I can do this? I would also like to update
the temp table in the stored procedure itself.
Something like as follows
CREATE TABLE #tmpTable --I dont want to specify the list of columns
SELECT * INTO #tmpTable FROM vwTest
UPDATE #tmpTable SET ......
Can anyone please help me with this?
Thank you
EricGos,
How about SELECT * INTO #mytemptable from viewname
Note that using * in production code is generally not considered good =practice, as things can change oif columns added or removed.
Mike John
Gos wrote:
> Hi,
> > I would like to create a temporary table is a stored procedure which
> contains all the same set of columns as one of my view in the
> database. Is there anyway I can do this? I would also like to update
> the temp table in the stored procedure itself.
> > Something like as follows
> > CREATE TABLE #tmpTable --I dont want to specify the list of columns
> > SELECT * INTO #tmpTable FROM vwTest
> > UPDATE #tmpTable SET ......
> > > Can anyone please help me with this?
> > Thank you
> Eric|||Hello,
Have you tried this:
-- Start
CREATE PROC usp_Test
AS
SELECT * INTO #tmpTable FROM vwCustomers
UPDATE #tmpTable SET CustomerID = 'BERGA' WHERE CustomerID = 'BERGS'
SELECT * FROM #tmpTable
GO
EXEC usp_Test
SELECT * FROM vwCustomers
--End
vwCustomers is a view in the Northwind Sample Database that I created.
You can find the SQL for the vwCustomers here:
http://www.ilopia.com/MSSQL/Tutorials/Views.aspx#PartitionedViews
Hope this helps!
--
Regards,
Kristofer Gafvert
http://www.ilopia.com - FAQ & Tutorials for Windows Server 2003, and SQL
Server 2000
Reply to newsgroup only. Remove NEWS if you must reply by email, but please
do not.
"Gos" <ericmoyer2345@.yahoo.com> wrote in message
news:a3cf3db2.0309281229.15c22814@.posting.google.com...
> Hi,
> I would like to create a temporary table is a stored procedure which
> contains all the same set of columns as one of my view in the
> database. Is there anyway I can do this? I would also like to update
> the temp table in the stored procedure itself.
> Something like as follows
> CREATE TABLE #tmpTable --I dont want to specify the list of columns
> SELECT * INTO #tmpTable FROM vwTest
> UPDATE #tmpTable SET ......
>
> Can anyone please help me with this?
> Thank you
> Eric|||Thanks a lot..
It works fine. I have a view which contains only the columns that are
required to be displayed on the User Interface. So, I thought that I
can use that view (SELECT * ) instead of giving a long list of
columns. Isn't it a good programming practice?
Gos