Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Jul 20, 1999

INSERT and ORDER BY in Creating Views

Question:
I have a problem with Views. If I make this query:
INSERT INTO ACHATS
SELECT DISTINCT VINS_ACHAT.PRO, VINS_ACHAT.Qtebouteille
FROM VINS_ACHAT
then this is a good one. But if I want to make a view with this query like this:
CREATE VIEW A_Ajout_achats AS
INSERT INTO ACHATS
SELECT DISTINCT VINS_ACHAT.PRO, VINS_ACHAT.Qtebouteille
FROM VINS_ACHAT
then there is an error on the INSERT.

I don't know how I can have a view which can do the same as my query. Moreover the ORDER BY don't goes on the CREATE VIEW. It creates an error. Do you know why ?

I am working this summer on an upsizing project from Access to SQL Server, and I really need the answer to my problem because I have to retype almost all the SQL code.

Answer:
Your first statement, the INSERT INTO, is data manipulation language (we say "DML"). It will insert records generated by the select into ACHATS. This is not just a query, it is an unrestricted insert statement. It is unrestricted because you will produce the cross join of whatever is in the PRO and QTEBOUTEILLE tables. Should there be some join condition here that limits the number of matches between tables?

I usually create views to limit access to data, in order to permit a user to view selected columns or selected rows of some relation. I would therefore change the language of your second statement to be:

CREATE VIEW A_AJOUT_ACHATS AS SELECT DISTINCT VINS_ACHAT.Qtebouteille FROM VINS_ACHAT
(What is the table name here? Isn't VINS_ACAHT the database name?)

Anyway, in order for the relation created by the view to be updatable, the FROM clause can only include one table.

Sorry, but Ordering is not supported for views.

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date