Unable to add raw values to the insert into SQL Server select query-CodePudding

This query is throwing an error

VALUES clause must match the number of columns specified in the INSERT statement

INSERT INTO [dbo].[CONSUMPTIONQTYMASTER] ([shop], [fgModelCode], 
                                          [VINNumber], [SerialNumber], 
                                          [consumptionQty], [itemCode], 
                                          [itemDescription], [grnNumber],
                                          [sku], [lotNumber], 
                                          [serial], [uom], 
                                          [vendorCode], [vendorName])
VALUES (10, '7FASTDOM15R05S2D0302', 
        'TIEV1-DPWO-0100', 'test serial 100', 
        2, (SELECT 
                STORE.itemCode, STORE.itemDescription, STORE.grnNumber, 
                STORE.sku, STORE.lotNumber, 
                STORE.serial, STORE.uom, 
                STORE.vendorCode, STORE.vendorName
            FROM 
                STORE 
            WHERE 
                STORE.SKU = '7B0101000032h22003'));

CodePudding user response：

Your subquery in the VALUES entry is messing things up. Change instead to something like:

INSERT INTO [table] ( [column a], [column b] ... )
SELECT 'Static Value A', 'Static Value B', ... -- other columns
FROM [source-table]
WHERE [condition]

CodePudding user response：

The VALUES instructiona has a specific syntax and you need to follow its rules.

This specific case is covered by Limits and Restriction paragraph

You can rewrite your query in this way:

INSERT INTO [dbo].[CONSUMPTIONQTYMASTER] (
    [shop], 
    [fgModelCode], 
    [VINNumber], 
    [SerialNumber], 
    [consumptionQty], 
    [itemCode], 
    [itemDescription], 
    [grnNumber],
    [sku], 
    [lotNumber], 
    [serial], 
    [uom], 
    [vendorCode], 
    [vendorName]
)
SELECT 
    10, 
    '7FASTDOM15R05S2D0302', 
    'TIEV1-DPWO-0100', 
    'test serial 100', 
    2, 
    STORE.itemCode, 
    STORE.itemDescription, 
    STORE.grnNumber, 
    STORE.sku, 
    STORE.lotNumber, 
    STORE.serial, 
    STORE.uom, 
    STORE.vendorCode, 
    STORE.vendorName
FROM 
    STORE 
WHERE 
    STORE.SKU = '7B0101000032h22003'