INSERT and ORDER BY in Creating Views

INSERT and ORDER BY in Creating Views

Question:
I have a problem with Views. If I make this query:

INSERT INTO ACHATSSELECT DISTINCT VINS_ACHAT.PRO, VINS_ACHAT.QtebouteilleFROM 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 ASINSERT INTO ACHATSSELECT DISTINCT VINS_ACHAT.PRO, VINS_ACHAT.QtebouteilleFROM 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.

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of