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

Advertisements
%d bloggers like this: