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

Share

Share

Rate