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.