CODEWITHSUNDEEP
sql-server
venkanna babu
venkanna babu

Reputation: 59

how to create a table with result set in sql server

I want to create a table to store result set of another query.

I have tried this:

create table avgcost_product (
    name varchar(50),
    productnumber varchar(50),
    averagecost money
)

After creating table I tried this query:

INSERT into avgcost_product SELECT * FROM
(SELECT p.[Name], p.ProductNumber,
        CONVERT(varchar, cost.AvgCost,1) as 'Average Cost'
FROM Production.Product p
CROSS APPLY fnGetAvgCost(p.ProductID) as cost
WHERE cost.AvgCost IS NOT NULL
ORDER BY cost.AvgCost desc)

But it shows error like:

Msg 1033, Level 15, State 1, Line 284

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

How I can write data I need in my table?

Upvotes: 3

Views: 625

Answers (2)

gofr1
gofr1

Reputation: 15977

Also you can create the table on the fly using SELECT INTO:

SELECT  p.[Name], 
        p.ProductNumber,
        CONVERT(varchar, cost.AvgCost,1) as [Average Cost] 
INTO avgcost_product
FROM Production.Product p
CROSS APPLY fnGetAvgCost(p.ProductID) as cost
WHERE cost.AvgCost IS NOT NULL

Upvotes: 0

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

Just remove the ORDER BY from the inner select or you can even simplify your query even more:

INSERT into avgcost_product 
SELECT p.[Name]
       , p.ProductNumber
       , CONVERT(varchar, cost.AvgCost,1) as 'Average Cost'
FROM Production.Product p
CROSS APPLY fnGetAvgCost(p.ProductID) as cost
WHERE cost.AvgCost IS NOT NULL

Upvotes: 5

Related Questions