CODEWITHSUNDEEP
sql-server
PhilBrown
PhilBrown

Reputation: 2999

Increment a variable in an INSERT statement

Is there a way to do the following:

DECLARE @startVal integer

SELECT 
    @startIdx = MAX(Range_val) 
FROM 
    [Bookings].[dbo].[Range] (nolock)

INSERT INTO
    [Bookings].[dbo].[Range]
VALUES
    (INCR(@startVal), 'someVal', 'someOtherVal'),
     (INCR(@startVal), 'someVal1', 'someOtherVal3'),
     (INCR(@startVal), 'someVal2', 'someOtherVal4'),

Where INCR() is some function that increments the variable. Instead of doing '@startIdx + 1', '@startIdx + 2', etc?

EDIT: This is not necessarily the table index. I know I can drop the table and recreate it the proper way (assuming this is the index), but that's not my question. Is there a way to increment a variable the way I have described?

Upvotes: 5

Views: 17406

Answers (4)

Alex K.
Alex K.

Reputation: 175758

If your just looking to make it easier to mess around with & cannot use an identity column here are a couple of ways to avoid specifying +1;

   declare @T TABLE (idx int identity(1, 1), f1 varchar(128), f2 varchar(128))
   insert into @T values
       ('someVal',  'someOtherVal'),
       ('someVal1', 'someOtherVal3'),
       ('someVal2', 'someOtherVal4')
   insert [Bookings].[dbo].[Range]
       select @startIdx + idx, f1, f2 from @T

Or (ids are assigned in an alphabetical not ordinal order based on the 1st field)

   insert [Bookings].[dbo].[Range]
      select @startIdx + row_number() over(order by name1) as n, *
          from (
                  select top 0     '' as name1, '' as name2 --header
                  union all select 'someVal',   'someOtherVal'
                  union all select 'someVal1',  'someOtherVal3'
                  union all select 'someVal2',  'someOtherVal4'
          ) T

Are you sure you want nolock?

Upvotes: 3

EricZ
EricZ

Reputation: 6205

As cetver mentioned, use Identity on Range table will be best choice.

Otherwise, I will use temp table with identity column 

DECLARE @inc_table TABLE (id INT IDENTITY(1,1), col1 VARCHAR(50), col2 VARCHAR(50) );

INSERT INTO
    @inc_table
VALUES
    ( 'someVal', 'someOtherVal'),
     ( 'someVal1', 'someOtherVal3'),
     ( 'someVal2', 'someOtherVal4'),

INSERT INTO   
    [Bookings].[dbo].[Range]
SELECT
    i.id + m.max_range_id as range_id
    ,i.col1
    ,i.col2
FROM @inc_table i 
INNER JOIN (
    SELECT 
        MAX(Range_id) as max_range_id
    FROM 
        [Bookings].[dbo].[Range] (nolock)
)  m
    ON 1=1

Upvotes: 1

12N
12N

Reputation: 99

Set that column to use an Identity. Every other solution is more prone to errors and will surely be harder to maintain, besides the huge load of WTFs that it'll get. If you insist on perpetuating the faulty design, go for cetver's answer.

Upvotes: -3

cetver
cetver

Reputation: 11829

/*
Option 1
*/
INSERT into Range
       (
              range_id
       )
SELECT MAX(range_id) + 1
FROM   Range

/*
Option 2:
create custom function
Option 3 (best choice):
use IDENTITY
*/

Upvotes: 0

Related Questions