473,241 Members | 1,569 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

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_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
4 2253
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Marko Poutiainen | last post by:
Situation: We had to make our SQLServer 2000 database multi-lingual. That is, certain things (such as product names) in the database should be shown in the language the user is using (Finnish,...
1
by: Witold Iwaniec | last post by:
Hi I have a procedure that calls a view. The view is built with some outer joins but it performs fine. If I run in Query Analyzer select count(*) from long_name_view where name_id = 'AAA'
7
by: Rick Caborn | last post by:
Does anyone know of a way to execute sql code from a dynamically built text field? Before beginning, let me state that I know this db architecture is built solely for frustration and I hope to...
2
by: harry | last post by:
Hi All ! I open a table in Query Analyzer by right clicking on it and selecting 'Open' and when the data is displayed in the view pane I would like to be able to edit it. It seems however that...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
1
by: ing42 | last post by:
I have a problem with inserting records into table when an indexed view is based on it. Table has text field (without it there is no problem, but I need it). Here is a sample code: USE test GO...
0
by: Melanie | last post by:
Hi - my problem is as follows: I'm writing an ASP.Net 2.0 app with C#. From the app, I call a stored procedure that updates some data in the app's database (SQL Server 2000). This stored procedure...
6
by: fniles | last post by:
I am using VB.NET 2003 and SQL Server 2000. I have a table with a datetime column type. When inserting into the table for that column, I set it to Date.Now.ToString("T") , which is something like...
16
priyan
by: priyan | last post by:
Hi all, I had created a view from a table employee from my database. View is created sucuessfully and when i inserted data into employee table it is also updated in the view but i tried to...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.