May 15, 2010 at 4:02 am
I've a solution. I want to found missing a ID in a table and use it to INSERT.
I have a table
CREATE TABLE [dbo].[MISSINGTABLE](
[K] [int] NOT NULL,
[ID] AS ('CD'+right('00000000'+CONVERT([varchar],[K],0),(7))) PERSISTED,
CONSTRAINT [PK_MISSINGTABLE] PRIMARY KEY CLUSTERED
(
[K] ASC
)
and data
INSERT INTO MISSINGTABLE
SELECT 3 AS K
UNION ALL SELECT 4 AS K
UNION ALL SELECT 7 AS K
UNION ALL SELECT 8 AS K
You can see my data missing 1,2,5,6 value (K)
And i've a SQL to INSERT INTO my table with misssing K
INSERT INTO MISSINGTABLE(K) VALUES(
coalesce(
(
SELECT top 1 temp.K FROM
(
select
ROW_NUMBER() over(ORDER BY K) K
FROM MISSINGTABLE
) temp
LEFT OUTER JOIN
MISSINGTABLE temp1 ON temp1.K = temp.K
WHERE temp1.K IS NULL
)
,(
SELECT MAX(K)+1 FROM MISSINGTABLE
)
,1
)
)
Any more better solution for my problem?
May 15, 2010 at 5:26 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply