Inserting into a tmp table using a View --- Please help using SQL Query Analyzer

[Dia]

Hi there,

I struggle to get this going i would like to insert data into 2 tmp
tables in a view.
If i run the code on it's own it works perfectly until i want to create

a view it complains about the INSERT
this is my code
Create view dbo.vew_Switche s
As
INSERT INTO tmpIns
Select
Distinct
BIV.DATE,
BIV.ID,
CA.NAME,
BIV.IND,
BIV.AMOUNT,
BIV.UNITS,
BIV.INAME,
MB.NO
from Cars BIV
LEFT JOIN MountainBikes MB
ON MB.ID = BIV.ID
AND MB.CLASS = BIV.CLASS
AND MB.NUMBER = BIV.NUMBER
AND MB.DATE = BIV.DATE
LEFT JOIN Caterpillars CA
ON CA.ID = MB.NO
where BIV.CLASS = 'SWCH'
and BIV.IND = 'IN'
AND BIV.UNITS = 0
AND BIV.AMOUNT <0
ORDER BY BIV.DATE ASC
------ Step 2 -------Into tmpOuts
Insert Into tmpOuts ---- All Switches In ----
Select
Distinct
BIV.DATE,
BIV.ID,
CA.NAME,
BIV.IND,
BIV.AMOUNT,
BIV.UNITS,
BIV.NAME,
MB.NO
from Cars BIV
LEFT JOIN Mountainbikes MB
ON MB.ID = BIV._ID
AND MB.CLASS = BIV.CLASS
AND MB.NUMBER = BIV.NUMBER
AND MB.DATE = BIV.DATE
LEFT JOIN Caterpillars CA
ON CA.ID = MB.NO
where BIV.CLASS = 'SWCH'
and BIV.IND = 'OUT'
AND BIV.UNITS = 0
AND BIV.AMOUNT <0
ORDER BY BIV.DATE ASC
----------------------Step 3 ----------------
Select
Distinct
ins.DATE,
ins.ID,
ins.NAME ,
insIND,
ins.AMOUNT/100 as AmountIn,
outs.IND,
outs.AMOUNT/100 as AmountOut,
outs.NAME

>From tmpIns ins

Join tmpOuts outs
ON OUTS.ID = INS.ID
where outs.NAME = ins.NAME
and outs.NO = ins.NO

----- truncate step ----
Truncate table tmpIns
Truncate table tmpOuts

[Ed Murphy]

Dia wrote:

I struggle to get this going i would like to insert data into 2 tmp
tables in a view.
If i run the code on it's own it works perfectly until i want to create
a view it complains about the INSERT

You need a stored procedure, not a view.

[Dia]

Ed Murphy wrote:

Dia wrote:

I struggle to get this going i would like to insert data into 2 tmp
tables in a view.
If i run the code on it's own it works perfectly until i want to create
a view it complains about the INSERT

You need a stored procedure, not a view.

I need a vew to link into a query in Access for a report, so it's not
possible to do this in a view???

If not then i'll have to work my way around running a stored procedure
BUT would really settle for a view

[Ed Murphy]

Dia wrote:

Ed Murphy wrote:

>Dia wrote:

>>I struggle to get this going i would like to insert data into 2 tmp
tables in a view.
If i run the code on it's own it works perfectly until i want to create
a view it complains about the INSERT

You need a stored procedure, not a view.

I need a vew to link into a query in Access for a report, so it's not
possible to do this in a view???

If not then i'll have to work my way around running a stored procedure
BUT would really settle for a view

So far, we've only seen what you want to insert into the temp
tables. We need to also see what you want to pull out of them
in the end, and then we may be able to rewrite it in a form
that can be used as a view.

[Erland Sommarskog]

Dia (fa************ ****@selestia.c o.uk) writes:

I need a vew to link into a query in Access for a report, so it's not
possible to do this in a view???

A VIEW is just a SELECT statement. You could use a multi-valued table
function, although I don't know what Access think of that.

You can probably make it without the temp tables though, by using derived
like below.

I would also question your use of DISTINCT. In my experience DISTINCT
is a keyword that you only occasionally have need for. If you find that
you need to use DISTINCT, it may be that you have insufficient join
conditions, or that you join against a subtable do an existennce, for
which you should use EXISTS instead. Or you are just putting it in to
cover your rear parts. However, DISTINCT calls for sorting operations, and
can be costly in performance.

Select Distinct
ins.DATE,
ins.ID,
ins.NAME ,
insIND,
ins.AMOUNT/100 as AmountIn,
outs.IND,
outs.AMOUNT/100 as AmountOut,
outs.NAME

From (Select Distinct
BIV.DATE, BIV.ID, CA.NAME, BIV.IND, BIV.AMOUNT, BIV.UNITS, BIV.INAME,
MB.NO
from Cars BIV
LEFT JOIN MountainBikes MB
ON MB.ID = BIV.ID
AND MB.CLASS = BIV.CLASS
AND MB.NUMBER = BIV.NUMBER
AND MB.DATE = BIV.DATE
LEFT JOIN Caterpillars CA
ON CA.ID = MB.NO
where BIV.CLASS = 'SWCH'
and BIV.IND = 'IN'
AND BIV.UNITS = 0
AND BIV.AMOUNT <0) AS ins

Join (Select Distinct
BIV.DATE, BIV.ID, CA.NAME, BIV.IND, BIV.AMOUNT, BIV.UNITS, BIV.NAME,
MB.NO
from Cars BIV
LEFT JOIN Mountainbikes MB
ON MB.ID = BIV._ID
AND MB.CLASS = BIV.CLASS
AND MB.NUMBER = BIV.NUMBER
AND MB.DATE = BIV.DATE
LEFT JOIN Caterpillars CA ON CA.ID = MB.NO
where BIV.CLASS = 'SWCH'
and BIV.IND = 'OUT'
AND BIV.UNITS = 0
AND BIV.AMOUNT <0) AS outs
ON OUTS.ID = INS.ID
where outs.NAME = ins.NAME
and outs.NO = ins.NO


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx