sql server (alternate): [quoted text, click to view]

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_Switches
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



[quoted text, click to view]


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

[quoted text, click to 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

[quoted text, click to 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

Dia (fareldia.jefferies@selestia.co.uk) writes:
[quoted text, click to 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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at