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
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.idASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Zberteoc
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.
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?)
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?)
@Lowfatspread:
Why predefining would be better?
Why predefining would be better?
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)
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)
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.
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.
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.
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.
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.