Set ANSI_NULLS ON
Set QUOTED_IDENTIFIER ON
Go
/** // ****** Object: Stored Procedure dbo. spOrdersInsert ******/
Alter procedure [dbo]. [spOrdersInsert]
(
@ CustomerID INT,
@ CartID VARCHAR (50 ),
@ Memo VARCHAR (1, 2000 ),
@ OrderID INT OUTPUT
)
AS
-- Ensures data consistency and starts to use transactions.
Begin tran OrdersInsert
/** // * Generate the Order */
Insert into Orders
(
CustomerID,
Memo
)
Values
(
@ Customerid,
@ Memo
)
-- Returns the currently generated orderid.
Select
@ Orderid = @ identity
/** // * Copy the current shopping cart of the current user to the Order details generated by the current user */
Insert into ordersdetail
(
Orderid,
Productid,
Quantity,
Unitcost
)
------------------------ Is there a problem ?????
Select
@ Orderid,
Shoppingcart. productid,
Quantity,
Products. UnitCost
FROM
ShoppingCart
Inner join Products ON ShoppingCart. ProductID = Products. ProductID
WHERE
CartID = @ CartID
Commit tran OrdersInsert
Through the insert from query, you can copy rows FROM one table to another or in the same table. For example, in the titles table, you can use the insert from query to copy information about all the books of a publisher to another table and make the table usable by the publisher. The insert from query is similar to the query of the generated table. The difference is that the rows are copied to the existing table.
You can also copy rows from one table to another by cutting and pasting the rows. For more information, see Add a new row in the Results pane.
When creating an insert from query, specify:
The database table (destination table) to which rows are copied ).
Copy the table (source table) of the row from it ). The source table or table is part of the subquery. If the table is copied, the source table is the same as the target table.
Columns in the source table for content replication.
The target column in the target table to which data is copied.
Defines the Search Condition for the row to be copied.
Sort order (if you want to copy rows in a specific order ).
"Group" option (if only summary information is copied ).
For example, the following query copies the title information from the titles table to an archive table named archivetitles. This query copies the four columns of all the titles of a publisher:
Insert into archivetitles
(Title_id, title, type, pub_id)
SELECT title_id, title, type, pub_id
FROM titles
WHERE (pub_id = '2013 ')
Note: To INSERT a value to a new row, use insert into to query.
You can copy the content of the selected or all columns in the row. In any case, the copied data must be compatible with the columns in the row to be copied. For example, if you copy the content of a column (such as the price column), the columns in the row to be copied must accept numeric data with decimal digits. If you want to copy the entire row, the target table must have a compatible column with the same physical location as the source table.
When an insert from query is created, the grid pane changes to reflect options that can be used to copy data. Added an "APPEND" column to allow specified columns to be copied.
Note that you cannot cancel the insert query operation. As a precaution, back up data before executing insert into query.
Create an insert from Query
Right-click the query designer window, point to the "change type" menu, and select the "insert from" command.
In the Select Table dialog box for insert from query, select the table (target table) to which the row is copied ).
Note that the query designer cannot determine which tables and views can be updated in advance. Therefore, in the "table name" list in the "Select Table for insert from query" dialog box, all available tables and views in the queried data connection are displayed, it even includes tables and views that may not be copied to rows.
Add the table (source table) from which the row will be copied to the query. For more information, see Add a table. If you are copying rows in a table, you can add the source table as the target table.
The data in the source table is listed in the input window in the Diagram pane.
In the rectangle that represents the table or table structured object, select the column name for content replication. To copy the entire row, select "* (all columns )".
The query designer adds the selected columns to the "columns" column in the grid pane.
In the "APPEND" column of the grid pane, select a target column for each column to be copied in the target table. If you want to copy the entire row, select the table name .*. The columns in the target table must have the same (or compatible) data type as those in the source table.
If you want to copy rows in a specific order, specify the sorting order. For more information, see sort query results.
In the "criterion" column, enter a search condition to specify the row to be copied. For more information, see specify search criteria.
If no search condition is specified, all rows in the source table are copied to the target table.
Note: When the column to be searched is added to the grid pane, the query designer also adds the column to the list of columns to be copied. If you want to use a column as a search condition but do not copy it, clear the check box next to the column name in the rectangle that represents the table or table structured object.
To copy the summary information, specify the "Group" option. For more information, see the values of all rows in the summary table.
When an insert from query is executed, no results are reported in the result pane. Instead, a message is displayed indicating the number of copied rows.
The following query copies the title information from the titles table to an archive table named archivetitles. This query copies the four columns of all the titles of a publisher:
Insert into archivetitles
(Title_id, title, type, pub_id)
SELECT title_id, title, type, pub_id
FROM titles
WHERE (pub_id = '2013 ')
