How to increment numbers on data insert?

  • I'm trying to load a table from a view.

    The target table is currently empty and has the following basic structure -

    producerid [int]

    productid [int]

    productname [varchar] 50

    The view draws from a couple of other tables and has the following basic structure -

    producerid

    productname

    What I'd like to be able to do is populate the target table from the view but also have the productid field populated automatically during the insert as an auto-incremented number starting at 1 and incrementing by 1.

    Realize this is probably very simple but am a complete newby at T-SQL and would appreciate any advice.

    Regards

    Steve

  • The property you are required is identity. But i am afraid that you can not define identity for a view itself.

    DBDigger Microsoft Data Platform Consultancy.

  • Create the target table with an identity column for ProductId.... like so....

    create table my_table

    (

    ProductId[int] identity(1,1),

    ProducerId[int],

    ProductName[varchar](50)

    );

    go

    --and then to insert into it:

    insert into my_table(ProducerId, ProductName)

    select ProducerId, ProductName from my_view;

    go

    Hope that helps.

  • identity column is the way to go. you can also look to increment it programatically.

    "Keep Trying"

  • Thanks to all who posted a response to my query.

    I'll change the target table to have an "identity" column asap.

    Gratefull regards to all!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply