Increment field value by 1

• xxxxxxxxxx

  Grasshopper

  Points: 16

  More actions

  April 24, 2008 at 6:46 pm

  #186959

  Hi All,

  I am wrote a simple Insert into query, that selects values from table A and inserts it into table B. The Primary key on table B does not have identity property turn on (by vendor design).

  I need help in writing a function or script that takes the max value on table B primary key and increments it by 1 for every record that is inserted into the table.

  For example table B primary key is DocID. For every record that I try to insert using my query DocID = max(docid) + 1.

  Thanks in advance for your help.

• Mahesh Bote

  SSCrazy Eights

  Points: 9107

  More actions

  April 25, 2008 at 1:21 am

  #806988

  [font="Verdana"]

  Assuming you got a stroed procedure.

  ...

  Declare @Id Int

  Select @Id = Max(DocID) From TableB

  Insert Into {TableB}

  Select @Id, A.Col1, A.col2... From TableA As A

  ...

  Mahesh

  [/font]

  MH-09-AM-8694

• Adrian Nichols-360275

  SSCertifiable

  Points: 7694

  More actions

  April 25, 2008 at 4:17 am

  #807041

  Hi,

  Without having SQL Server 2005 available at the moment, you can try something like this:

  -- Test Environment

  DECLARE @tableA TABLE (

  col1 VARCHAR(2)

  )

  DECLARE @tableB TABLE (

  ident INT,

  col1 VARCHAR(2)

  )

  INSERT INTO @tableB VALUES (1, 'AB')

  INSERT INTO @tableB VALUES (2, 'CD')

  INSERT INTO @tableA VALUES ('EF')

  INSERT INTO @tableA VALUES ('GH')

  INSERT INTO @tableA VALUES ('IJ')

  INSERT INTO @tableA VALUES ('KL')

  -- Solution

  SELECT b.ident + ROW_NUMBER() OVER(ORDER BY a.col1 ASC),

  a.col1

  FROM @tableA a

  LEFT JOIN @tableB b

  ON b.ident = (SELECT MAX(ident) FROM @tableB)

  In theory, it should work... But theory doesn't rule the pratical world!! :crazy:

  ---

  
  Ade

  A Freudian Slip is when you say one thing and mean your mother.
  For detail-enriched answers, ask detail-enriched questions...[/url]