I have 2 stored procedures. Both insert records into table variables, and each of those table variable has an identity column. One procedure executes and the other gives an error telling me that I am explicitly trying to insert into the identity column (which I'm not). Here is the code from the 2 procs:
This one works:
DECLARE @BestSellingSkus TABLE
(RowNumber int IDENTITY UNIQUE,
Product_id varchar(40),
Sku_id varchar(40),
Quantity int,
Is_NLA int,
Stock_level int,
List_price numeric(19,7),
Sale_price numeric(19,7))
/*Select all products and child skus into one massive temp table for working with.*/
/*Uses the quantity field to get how many items per sku were sold - we want the one that sold the most items */
SELECT dcpc.product_id,
ds.sku_id,
CASE WHEN dip.on_sale > 0
THEN COALESCE(dip.sale_price,0)
ELSE COALESCE(dip.list_price,0)
END 'effective_price',
di.quantity,
dks.is_nla,
dinv.stock_level,
ds.list_price,
ds.sale_price
INTO #MasterProducts
FROM dcs_sku AS ds
JOIN dk_sku AS dks
ON (ds.sku_id = dks.sku_id)
LEFT OUTER JOIN dcspp_item AS di
ON (ds.sku_id = di.catalog_ref_id)
LEFT OUTER JOIN dcspp_item_price AS dip
ON (di.price_info = dip.amount_info_id)
LEFT OUTER JOIN dcs_inventory AS dinv
ON (ds.sku_id = dinv.catalog_ref_id)
LEFT OUTER JOIN dcs_prd_chldsku AS dcpc
ON (ds.sku_id = dcpc.sku_id)
WHERE ds.sku_id IS NOT NULL AND dcpc.sku_id IS NOT NULL
AND dcpc.product_id IN (SELECT master_number from dk_sku)
-- AND dcpc.product_id IN (SELECT master_number = '3401028')
/*Get just distinct product/sku records into temp table to process - gets row with greatest quantity sold*/
INSERT INTO @BestSellingSkus
SELECT product_id,
sku_id,
max(quantity ) AS Quantity,
is_nla,
stock_level,
list_price,
sale_price
FROM #masterproducts
-- where product_id = '031688'
GROUP BY product_id, sku_id, is_nla, stock_level, list_price, sale_price
ORDER BY product_id, is_nla ASC, quantity DESC, stock_level DESC, list_price DESC, sale_price DESC, sku_id
This one doesn't unless I list out each column for the insert:
DECLARE @BestSellingSkuAttribs TABLE
(RowNumber int IDENTITY UNIQUE,
product_id varchar(40),
sku_id varchar(40),
is_nla int,
sequence_num int,
value varchar(80))
INSERT INTO
@BestSellingSkuAttribs
SELECT dspc.product_id,
ds.sku_id,
dks.is_nla,
dav.sequence_num,
dav.value
FROM dcs_sku AS ds
LEFT OUTER JOIN dk_sku AS dks
ON (ds.sku_id = dks.sku_id)
LEFT OUTER JOIN dk_sku_attribute_values AS dsav
ON (ds.sku_id = dsav.sku_id)
LEFT OUTER JOIN dk_attribute_values AS dav
ON (dsav.attrib_value_id = dav.attrib_value_id)
LEFT OUTER JOIN dcs_prd_chldsku AS dspc
ON (ds.sku_id = dspc.sku_id)
WHERE dspc.product_id IN (SELECT product_id FROM dcs_prd_chldsku)
--WHERE dspc.product_id = 'p3301063'
AND ds.sku_id IS NOT NULL AND dspc.sku_id IS NOT NULL
GROUP BY dspc.product_id, ds.sku_id, dks.is_nla, dav.sequence_num, dav.value
ORDER BY dspc.product_id, dks.is_nla ASC, dav.sequence_num ASC, dav.value
I'm beating my head against the wall trying to find the difference! Your help is greatly appreciated!!
ASKED:
May 21, 2009 6:33 PM GMT
UPDATED:
May 30, 2009 1:59:05 AM GMT