Geek Answers Handbook

Variable increment in an INSERT statement

Is there any 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 this variable. Instead of doing "@startIdx + 1", "@startIdx + 2", etc.?

EDIT: This is an optional index table. I know that I can discard the table and recreate it appropriately (assuming it's an index), but that's not my question. Is there a way to increment a variable as I described?

+4
source share
4 answers

If you just want to facilitate communication and cannot use the Identifier column, these are several ways to avoid +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 (identifiers are assigned in alphabetical order out of 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 ?

+2
source

As mentioned above, it is best to use an Identity on Range table.

Otherwise, I will use a temporary table with an 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
+1
source 
 /* 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 */
0
source

Set this column to use Identity. Every other solution is more error prone and will certainly be harder to support than the huge WTF load it will receive. If you insist on perpetuating a faulty design, go get a cetver answer.

-1
source

More articles:


All Articles