How can I put this into a temp table

How Can I put the attached SQL View into a temp table?

Here is MS guide but I am little clueless how to modify it to my code.

Please advise
CREATE VIEW [dbo].[MetricsReport]
AS
SELECT     StoreId, Company, WeekNumber, SalesWOTax / CarCount AS TicketAvg, (AmountJobsSold - JobsDeclined + JobsSaved) / CarCount AS WrittenTicket, FlagTime / CarCount AS FlagTime, 
                      CAST((CourtesyInspectionsAccepted + CourtesyInspectionsDeclined) / CarCount * 10 AS VARCHAR(100)) + '%' AS PercentCIOffered, 
                      LaborRevenue / CarCount AS EffRate, JobsSold / (JobsSold + JobsDeclined + JobsSaved) AS SalesSuccess, Metrics.UserId
FROM         dbo.Metrics inner join dbo.Stores on Metrics.StoreId = Stores.id

Open in new window

goretailproAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

dougaugCommented:
Try this:

select *
into #yourtemptable
from MetricsReport
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
ZberteocCommented:
You can as per above comment but the question is why would you do that? A view behaves pretty much like a table so you don't need to store its content in a temp table.
0
LowfatspreadCommented:
dougaug sugestion will work although you maybe better off actually predefining you temporary table
before inserting the rows into it...

declare/create your table
 ...

insert into mytable select .... from myview where ...

but what is it you want to do with the rows once they are in the table....
what other features do you require on the table (e.g. a row number / unique id additional columns?)



0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

ZberteocCommented:
@Lowfatspread:

Why predefining would be better?

0
LowfatspreadCommented:
no overriding reasons for predefining

1) probably less maintenance overhead (helps enforce good practice of no select * usage in production code)
2) you use exactly the table columns/features that you require
3) you can use a table variable (more efficient if table is small)
4) potentially easier to control/define column attributes (e.g. UNIQUE constraints, Identity columns (define a new one))
5) define once , but allows you to select between several possible initial population sources (only 1 select into for a table is allowed per batch)

0
ZberteocCommented:
select * has nothing to do with predefining, you can use select col1, col2... into...

you can create an identity column on the fly with select identity(int, 1,1)  as id, col1, col2, ... into...

the types of the columns from the select statement will actually be duplicated into the new table.

if you need indexes on a table is actually more efficient to populate it first and create the indexes after, which can easily be done after select ... into

 it is true that you can't select ... into in a table variable and you can't use select into with a stored procedure. You have to pre define a table in order to be able to insert to it from EXEC proc:

INSERT INTO table EXEC procedure

Overall I would say select .. into is safe to use if you don't have some specific needs that will not be compatible with it.
0
LowfatspreadCommented:
select * has nothing to do with predefining, you can use select col1, col2... into...
never said it did... you often see it however... much prefer select col1, .. into if its going to be used..

you can create an identity column on the fly with select identity(int, 1,1)  as id, col1, col2, ... into...
of course but don't forget to remove the identity attribute from the existing column
select identity(int, 1,1)  as id, coalesce(col1, col1) as col1,... into  assuming col1 is identity col of source table

the types of the columns from the select statement will actually be duplicated into the new table.

if thats what you want... the example is using calculations which may benefit from a more precise initial definition
e.g. Decimal(5,2)

if you need indexes on a table is actually more efficient to populate it first and create the indexes after, which can easily be done after select ... into

true as long as your correct clustering index is initially defined and the tablescan requirement doesn't become overly burdensome index. Indexes are usually created later to reduce the initial logging overheads of the data load phase.


0
ZberteocCommented:
Like I said, if there are no specific needs that will be incompatible with select .. into then go ahead and use it. There are no any good practice and/or general considerations in regards to it to make the predefining path more preferable. That is not an issue.
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.