Blog Post

Data Warehousing Tip: Surrogate Key Generation

,

If IDENTITY insert is disabled or simply not available in a particular database or a database architecture, you’ll want to do a table based surrogate key assignment or use INSERT w ROW_NUMBER() with the OVER clause. I prefer using ROW_NUMBER().

USE [SomeDB]

CREATE TABLE [dbo].[testTgtTable](

[srgKey] [int] NULL,

[colA] [nchar](10) NULL,

[colB] [nchar](10) NULL

)

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[testSrcTable](

[colA] [nchar](10) NULL,

[colB] [nchar](10) NULL

)

GO

INSERT INTO testSrcTable

SELECT ‘hear’, ‘no evil’ UNION ALL

SELECT ‘see’, ‘no evil’ UNION ALL

SELECT ‘speak’, ‘no evil’

DECLARE @someIntVar int

SET @someIntVar = (SELECT ISNULL(MAX(SrgKey),0) SK

FROM dbo.testTgtTable)

INSERT INTO testTgtTable (SrgKey, colA, colB)

SELECT

ROW_NUMBER() OVER(ORDER BY [colA]) + @someIntVar SK

, [colA]

, [colB]

FROM

testSrcTable

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating