By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,570 Members | 970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,570 IT Pros & Developers. It's quick & easy.

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

P: n/a
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_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
>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

Nov 30 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.
Nov 30 '06 #2

P: n/a
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

Nov 30 '06 #3

P: n/a
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.
Nov 30 '06 #4

P: n/a
Dia (fa****************@selestia.co.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****@sommarskog.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
Nov 30 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.