Error inserting into table with identity column

45 pts.
Tags:
SQL 2000
SQL stored procedures
Stored Procedures
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!!

Answer Wiki

Thanks. We'll let you know when a new response is added.

It is always best practice to list out the columns of the table you are inserting into. This way you don’t have to worry about issues like this.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JennyMack
    Hi there, I noticed that you didn't specify which program you are using -- could you please provide more information? Thanks, Jenny Community Manager
    4,280 pointsBadges:
    report
  • Bleeb
    Sorry - it's SQL 2000
    45 pointsBadges:
    report
  • carlosdl
    I agree, it is a best practice to list out the columns, but it is strange that it didn't work without doing that.
    70,220 pointsBadges:
    report
  • Brijesh
    Hi,

    Check the resource SET IDENTITY_INSERT and follow this syntax which may resolve
    10,185 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following