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

About Sal De Loera
I have been involved in Information Technology, specifically Data Warehousing and BI, for over 8 years. Over these years I have assumed the following roles: Software Engineer Intern, Programmer/Analyst, Sr. Data Warehouse Engineer, Data Architect, Director of Information Technology, and as most recently as a Data Warehouse / BI consultant. Additionally, my IT experience spans the following industries: Insurance, Restaurant, Mass Media, Education, and Local Government. I have created this blog primarily as a means to not only share information and case studies regarding Business Intelligence, Data Warehousing, and Data Modeling, but also receive input from the community on some of the topics that I'l cover. Looking forward to your feedback and to the exciting opportunities that come with new people and technology!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: