TSQLExamples/Example-AddRecordsToAdventureWorksLT2022.sql at main · mcarr10/TSQLExamples · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
/* This script adds two new records to AdventureWorksLT2022 */
USE [AdventureWorksLT2022]


-- Insert a new record into SalesOrderHeader
INSERT INTO SalesLT.SalesOrderHeader
(
    OrderDate,
    DueDate,
    ShipDate,
    Status,
    OnlineOrderFlag,
    PurchaseOrderNumber,
    AccountNumber,
    CustomerID,
    ShipToAddressID,
    BillToAddressID,
    ShipMethod,
    CreditCardApprovalCode,
    SubTotal,
    TaxAmt,
    Freight,
    Comment
)
VALUES
(
    GETDATE(),
    GETDATE() + 10,
    NULL,  -- ShipDate
    1,  -- Status
    1,  -- OnlineOrderFlag
    'PO12345',  -- PurchaseOrderNumber
    '10-4020-000000',  -- AccountNumber
    (SELECT TOP 1 CustomerID FROM SalesLT.Customer),  --Use existing customer
    1001,  -- ShipToAddressID
    1001,  -- BillToAddressID
    'UPS',  -- ShipMethod
    '0457-0189-1234',
    1000.00,  -- SubTotal
    80.00,  -- TaxAmt
    30.00,  -- Freight
    NULL
);

-- Get the generated SalesOrderID for use in SalesOrderDetail
DECLARE @SalesOrderID INT = SCOPE_IDENTITY();

-- Insert a new record into SalesOrderDetail
INSERT INTO SalesLT.SalesOrderDetail
(
    SalesOrderID,
    OrderQty,
    ProductID,
    UnitPrice,
    UnitPriceDiscount
)
VALUES
(
    @SalesOrderID,
    10,
    712,
    100.00,
    0.00
);

INSERT INTO SalesLT.SalesOrderHeader
(
    OrderDate,
    DueDate,
    ShipDate,
    Status,
    OnlineOrderFlag,
    PurchaseOrderNumber,
    AccountNumber,
    CustomerID,
    ShipToAddressID,
    BillToAddressID,
    ShipMethod,
    CreditCardApprovalCode,
    SubTotal,
    TaxAmt,
    Freight,
    Comment
)
VALUES
(
    GETDATE(),
    GETDATE() + 7,
    GETDATE() + 7,  -- ShipDate
    1,  -- Status
    1,  -- OnlineOrderFlag
    'PO-A7856',  -- PurchaseOrderNumber
    '10-4010-000234',  -- AccountNumber
    (SELECT TOP 1 CustomerID FROM SalesLT.Customer),  --Use existing customer
    1002,  -- ShipToAddressID
    1002,  -- BillToAddressID
    'UPS',  -- ShipMethod
    '0345-01795-5678',
    95.00,  -- SubTotal
    9.50,  -- TaxAmt
    12.00,  -- Freight
    NULL
);

-- Get the generated SalesOrderID for use in SalesOrderDetail
DECLARE @SalesOrderID2 INT = SCOPE_IDENTITY();

-- Insert a new record into SalesOrderDetail
INSERT INTO SalesLT.SalesOrderDetail
(
    SalesOrderID,
    OrderQty,
    ProductID,
    UnitPrice,
    UnitPriceDiscount
)
VALUES
(
    @SalesOrderID2,
    10,
    709,
    9.50,
    0.00
);