INSERT INTO ... SELECT problems

[CmPaiva]

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?

For more info, the SQL I 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)
Thanks,
Carlos Paiva

 

[checkai]

can you do an identity on the table getting inserted into?

 

[CmPaiva]

No, Table has another column that is identity (Id).
this column (MovId) is an incremental number for each MovType(another column in table).Thus I need to do the insert and create an increment. This could be done with a cursor, and steping throught the selection. What i'm trying to do is optimizing the process, since i can have thousens of records to insert.
Thats why i've think in INSERT INTO ... SELECT. The only problem is that i couldnt find a way to have MovId incremented for each record in the bulk insert.

Thanks for your time,

Carlos Paiva