Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    73

    Unanswered: Using a SP to merge data

    I am using the SP below to try to copy some data from one table to another and I am getting an error. Does anyone have any idea what would be causing it.
    Code:
    Alter Procedure usp_ImportData
    
    As
    
    truncate table tblLineItems
    
    set identity_insert tblLineItems on
    
    INSERT INTO
    	tblLineItems (ReportPreparer, ReportDate, REV, CalDate, Limits, RMAReport, FailureCause, DispositionofProduct, ReturntoStock, Trash)
    SELECT
    	a.[Report Preparer], a.[Report Date], a.[REV], a.[Cal Date], a.[Limits], a.[RMA Report], a.[Failure Cause], a.[Disposition of Product], a.[Return to Stock], a.[Trash]
    
    FROM
    	tblRMA a
    
    INNER JOIN
    	tblLineItems b ON (a.[RMA Number]= b.RMANumber)
    
    set identity_insert tblLineItems off
    The error I am getting is "Explicit Value must be specified for identity column in table 'tblLineItems' when IDENTITY_INSERT is set to ON"

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You need to get rid of the IDENTITY_INSERT. You have an identity column on this table(probably the primary key) which is and identity column (int column that increments by one each time a record is inserted). The SET IDENTITY_INSERT ON command allows you to insert numbers into here instead of having it autogenerated.

    If you choose to do this, you need to specify the column in both your select statement and insert column list.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Apr 2004
    Posts
    73
    When I remove the identity_insert it just deletes all the records in the table.

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    The IDENTITY_INSERT has nothing to do with that.

    This truncate table tblLineItems means to delete all the records. Further, it's a non-logged operation.

    This isn't a production system you're trying this out on is it?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Apr 2004
    Posts
    73
    No I am testing this in a sperate copy of the database.

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Well, that's good. Does what's going make sense now?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  7. #7
    Join Date
    Apr 2004
    Posts
    73
    So I got rid of the truncate line and now it is inserting the records but it doesn't match the records up. I dont want to insert new records I want to try to copy records into matching records where the two RMA Numbers match. if that makes sense.
    Last edited by thatdude; 04-27-04 at 21:56.

  8. #8
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    UPDATE li
    SET
    li.ReportPreparer = a.[Report Preparer],
    li.ReportDate = a.[Report Date],
    li.REV = a.[REV],
    li.CalDate = a.[Cal Date],
    li.Limits = a.[Limits],
    li.RMAReport = a.[RMA Report],
    li.FailureCause = a.[Failure Cause],
    li.DispositionofProduct = a.[Disposition of Product],
    li.ReturntoStock = a.[Return to Stock],
    li.Trash = a.[Trash]
    FROM
    tblLineItems li
    INNER JOIN tblRMA a ON li.RMANumber = a.[RMA Number]

    Also, you should look at buying Sam's Teach Yourself Transact-SQL in 21 Days.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  9. #9
    Join Date
    Apr 2004
    Posts
    73
    I see what I was doing wrong now. Thanks for all the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •