Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2002
    Location
    Portugal
    Posts
    5

    Unanswered: INSERT INTO ... SELECT Problem

    Hi, Tkx in advance for your help.

    I need to do a INSERT INTO ... SELECT , but one of the destination field must be a sequencial nr, based in MAX(Field)+1 for a WHERE TypeFld=<cond> over the destination table.

    The question is How do I instruct SQL to the sequencial number?

    I did a function that returns the starting nr (Max(Field)+1), but after insert into, all selected records have in the desired field, Max(Field)+1.
    How can I have this value incremented?

    Thanks
    Carlos Paiva

  2. #2
    Join Date
    Nov 2002
    Location
    Portugal
    Posts
    5

    Arrow

    For more info the SQL used is:

    INSERT INTO Stocks (MovId, MovData, MovType, Talão, IdProducto, Quantidade, IdFornecedor, Compra, Venda, Documento, UserId, TerminalId)
    SELECT dbo.GetNewMovId(1), GETDATE(), 1, @NrTalão, Código, Quantidade, 0, 0, 0, '', @UserId, @TerminalId
    FROM FactBody
    WHERE (TransId = @TransId) AND (Tipo = 1)

    It was Column MovId, that I want Incremented, starting at Return value of dbo.GetNewMovId(1) function.

    Sorry by my bad english (I'm from Portugal)
    Carlos Paiva

  3. #3
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Two options: Both depend on MovID being an Identity field as described

    One: Allow the native Identity function to input this field. Just don't import anything into that field, SQL will update it automatically with the next available value. However you would not have control of the specific MovID (number) assigned.

    Two: Use the Identity_Insert function to allow updating of the Identity field. If your NewMovID is a good solid Identity field this should not be a problem.

    Set Identity_Insert Stocks ON
    remainder of your code
    Set Identity_Insert Stocks OFF

  4. #4
    Join Date
    Nov 2002
    Location
    Portugal
    Posts
    5

    Unhappy

    bowlive, thanks for your answer.

    The problem is that MovId IS NOT an identity field. It is a second identity (Moviment number of movement type xxx). The table has an identity field named Id.

    Any sugestions?
    Carlos Paiva

  5. #5
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Not sure If I fully understand the problem, can you post both the table structure (fieldnames, datatypes, etc) and the code you use to determine the input for MovID?

  6. #6
    Join Date
    Nov 2002
    Location
    Portugal
    Posts
    5
    Stocks is a table that keeps track of stock movement info of products.

    Sailed products for a transaction X, Reside in FactBody in the following format:

    TABLE FACTBODY:
    TransId int - Main transaction number
    Codigo int - Product Id
    ... Other unimportant information.

    TABLE STOCKS:
    Id int identity - Stock Table autonumber
    MovId int - Movement Number (1 to N by each movement type)
    MovType int - Type of movement to be done
    ... Other info unimportant

    What I Need Is:

    For a TransId in FACTBODY, create a bulk insert into STOCKS, having
    MovId correctly ordered for each row, starting at last existing MovId+1,
    for the selected MovType.

    I'm trying to do this using:

    INSERT INTO STOCKS (MovId, MovType,...)
    SELECT <WhatINeed>, 1,...
    FROM FACTBODY
    WHERE TransId=xxx AND MovType=1

    Hope you understand.
    Carlos Paiva

  7. #7
    Join Date
    Nov 2002
    Location
    Portugal
    Posts
    5
    baolive

    Hi,

    Did it make it clear?

  8. #8
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Did you get the email that I sent directly to you yesterday?

  9. #9
    Join Date
    Feb 2004
    Posts
    199
    Another way is to use CURSOR

  10. #10
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    That is the thought that I'm trying to work with.
    Update all of the records in Stocks with everything except MovId.
    Create a temp table containing all the records with [null] in MovId.
    Run a cursor against the temptable that updates MovId in Stocks.
    Problem is I suck at Cursors and haven't had the time to work out how this would work.

    Brent

Posting Permissions

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