CODEWITHSUNDEEP
sql-server-2008auto-increment
Filipe Costa
Filipe Costa

Reputation: 664

SQL server insert multiple rows and incrementing a int column

I have some rows in a table and need to transfer them to another table. In the destination table i need also to add a field with an incremental value.

I'm doing the following, but i know that something in the insert is wrong, because the incremented 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 so the value is incremented correcty?

Thank you.

EDIT:

I made a small change in the query, and now it works. I just insert a SELECT in the 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 with the incremented value.

Upvotes: 3

Views: 10494

Answers (4)

Gurpreet Singh
Gurpreet Singh

Reputation: 1731

In my case , i Inserted rows sequentially using the same business logic. I cannot use auto increment as i have to import old data also into this column. Once you have imported the data then u may go for updating the column for auto increment .

Upvotes: 0

Aaronontheweb
Aaronontheweb

Reputation: 8394

IDENT_CURRENT won't update until the transaction commits, therefore its value remains constant until you insert.

Here are three options for fixing this issue:

  1. Use some kind of counter (@newRowNum) such that for each row in your SELECT query, @newRowNum = @newRowNum +1, and thus your intCodInterno number = IDENT_CURRENT() + @newRowNum. This would probably require a lot of hacking to work though. Don't recommend it.

  2. Insert your rows sequentially using the same business logic you have now - it will be tremendously less performant, however. Don't recommend it.

  3. Set that column in your destination table to be an identity column itself. This is by far the best way to do it.

If you need a custom identity function (I assume there's a reason you're not using an identity column now), you can create one using some of the steps outlined above: http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

Upvotes: 3

NoAlias
NoAlias

Reputation: 9193

Remove the part with intCodInterno and in SQL Server use the Identity property to automatically increment it for you.

Upvotes: 3

Jahan Zinedine
Jahan Zinedine

Reputation: 14874

the IDENT_CURRENT('Emp_VISOT.dbo.TBL_GCE_ARTIGOS')+1 evaluated once when you want to run the query and all the rows will get the same id.

first solution is to iterate over the select result by a loop construct like cursor or whatsoever and insert the incremented index(you do that)

second solution is to make that column in destination table identity

Upvotes: 3

Related Questions