Found missing ID and INSERT with it

• Paul White

  SSC Guru

  Points: 150468

  More actions

  May 15, 2010 at 5:26 am

  #1167901

  Setup:

  CREATE TABLE dbo.MissingTable

  (

  K INTEGER NOT NULL,

  ID AS 'CD' + RIGHT(100000000 + K, 7) PERSISTED,

  CONSTRAINT [PK MissingTable K]

  PRIMARY KEY CLUSTERED (K)

  );

  GO

  INSERT dbo.MissingTable (K)

  SELECT 3 UNION ALL

  SELECT 4 UNION ALL

  SELECT 7 UNION ALL

  SELECT 8;

  GO

  One possible solution:

  INSERT dbo.MissingTable (K)

  SELECT n

  FROM dbo.Numbers ((SELECT TOP (1) M.K FROM dbo.MissingTable M ORDER BY M.K DESC))

  EXCEPT

  SELECT M.K

  FROM dbo.MissingTable M;

  Uses:

  SET ANSI_NULLS ON

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE FUNCTION dbo.Numbers

  (

  @Count BIGINT

  )

  RETURNS TABLE

  WITH SCHEMABINDING

  AS RETURN

  WITH

  E1 AS

  (

  SELECT n = 1 UNION ALL

  SELECT 2 UNION ALL

  SELECT 3 UNION ALL

  SELECT 4 UNION ALL

  SELECT 5 UNION ALL

  SELECT 6 UNION ALL

  SELECT 7 UNION ALL

  SELECT 8 UNION ALL

  SELECT 9 UNION ALL

  SELECT 10

  ), -- 1E1 rows

  E2 AS (SELECT X.n FROM E1 X CROSS JOIN E1), -- 1E2 rows

  E4 AS (SELECT X.n FROM E2 X CROSS JOIN E2), -- 1E4 rows

  E8 AS (SELECT X.n FROM E4 X CROSS JOIN E4), -- 1E8 rows

  Numbers AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM E8)

  SELECT TOP (@Count)

  N.n

  FROM Numbers N

  ORDER BY

  N.n ASC;

  GO

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi