SQL server inserts multiple rows and increases int index

Question

Good afternoon,

I have several rows in a table and need to move them to another table.

In the destination table, I also need to add a field with an incremental value.

I do this, but I know that something in the insert is wrong, because the incremental value (intCodInterno) is always the same:

INSERT INTO Emp_VISOT.dbo.TBL_GCE_ARTIGOS
        ( strCodigo ,
          strDescricao ,
          intCodInterno ,
          intCodTaxaIvaCompra ,
          intCodTaxaIvaVenda ,
          strCodCategoria ,
          strAbrevMedStk ,
          strAbrevMedVnd ,
          strAbrevMedCmp ,
          bitAfectaIntrastat
        )(
        SELECT  A.Artigo ,
                a.Descricao ,
                IDENT_CURRENT('Emp_VISOT.dbo.TBL_GCE_ARTIGOS')+1,
                '3' ,
                '3' ,
                '1' ,
                'Un' ,
                'Un' ,
                'Un' ,
                '0'
        FROM PRIVESAM.DBO.Artigo A)

What do I need to change for the value to be fixed?

Thank.

EDIT:

I made a small change to the request, and now it works. I just insert SELECT into IDENT_CURRENT inside brackets:

(SELECT IDENT_CURRENT('Emp_VISOT.dbo.TBL_GCE_ARTIGOS')+1)

I got all the rows that I need from the old table to the new one with an added value.

Thank you all for your help.

Answer 1

IDENT_CURRENT('Emp_VISOT.dbo.TBL_GCE_ARTIGOS')+1 , , .

, , , ( )

, identity

Answer 2

intCodInterno SQL Server , .

Answer 3

IDENT_CURRENT , , .

:

• - (@newRowNum), SELECT @newRowNum = @newRowNum +1, , , intCodInterno = IDENT_CURRENT() + @newRowNum. , , . .

• , -, , . .

• . , , .

( , , ), , : http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

Answer 4

In my case, I inserted the rows sequentially using the same business logic. I cannot use auto-increment because I need to import old data also into this column. After you have imported the data, you can proceed to update the column to automatically grow.