Find the maxId and update the ID by 1 when a null is found

• SQLTestUser

  Hall of Fame

  Points: 3997

  More actions

  December 20, 2016 at 6:14 pm

  #332909

  I have a table as below

  TableA

  AssetName nvarchar(50)

  AssetID smallint

  SomeID smallint

  ActiveOrnot bit

  I have to insert new values but the new values should have the assetID incremented by 1. So something like

  Insert into TableA (AssetName,AssetID,SomeID,ActiveOrNot) Value ('some', Select MAX(AssetID) + 1, 1, True) which doesnot work or can i update after i insert null values for the new rows ?

  thanks,

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  December 20, 2016 at 9:09 pm

  #1919075

  First some sample data:

  USE tempdb; -- a safe place for testing on a dev/sandbox instance

  GO

  -- create table for storing sample data

  CREATE TABLE dbo.TableA

  (

  AssetName nvarchar(50),

  AssetID smallint,

  SomeID smallint,

  ActiveOrnot bit

  );

  -- insert 3 records of sample data with a value for AssetID and 3 where AssetID is null

  INSERT dbo.TableA

  SELECT TOP (3)

  'xxx',

  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),

  abs(checksum(newid())%1000)+1,

  abs(checksum(newid())%2)

  FROM sys.all_columns

  UNION ALL

  SELECT TOP (3)

  'xxx',

  NULL,

  abs(checksum(newid())%1000)+1,

  abs(checksum(newid())%2)

  FROM sys.all_columns;

  Now you have:

  AssetName AssetID SomeID ActiveOrnot

  ----------- ------- ------ -----------

  xxx 1 962 1

  xxx 2 764 1

  xxx 3 385 0

  xxx NULL 867 1

  xxx NULL 195 0

  xxx NULL 818 1

  To assign values to the null assetIDs you could do this:

  DECLARE @maxid int = (SELECT MAX(AssetID) FROM dbo.TableA);

  WITH Nulls AS

  (

  SELECT AssetID, AssetID_new = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))+@maxid

  FROM dbo.TableA

  WHERE assetID IS NULL

  )

  UPDATE Nulls

  SET AssetID = AssetID_new;

  Now you have:

  AssetName AssetID SomeID ActiveOrnot

  ----------- ------- ------ -----------

  xxx 1 962 1

  xxx 2 764 1

  xxx 3 385 0

  xxx 4 867 1

  xxx 5 195 0

  xxx 6 818 1

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  December 21, 2016 at 6:41 am

  #1919131

  SQLTestUser (12/20/2016)

  ---

  ...the new values should have the assetID incremented by 1. So something like

  Insert into TableA (AssetName,AssetID,SomeID,ActiveOrNot) Value ('some', Select MAX(AssetID) + 1, 1, True)

  Is there a reason not to use an IDENTITY column? SQL Server can manage this much better internally than any code.

  https://msdn.microsoft.com/en-us/library/ms186775.aspx

  If it needs to be something independent of the table, you'd need SQL Server 2012 or later to use a SEQUENCE.

  https://msdn.microsoft.com/en-us/library/ff878091.aspx

• SQLTestUser

  Hall of Fame

  Points: 3997

  More actions

  December 21, 2016 at 3:16 pm

  #1919245

  That worked, thank you for the solution.