Migration: Generate Pivotal ID in SQL 2005

Below is the sample code on how to generate Pivotal ID using the new ROW_NUMBER() feature in SQL 2005.

USE AdventureWorksLT
GO
DECLARE @Last_Id AS BINARY(8)

SELECT 
    @Last_Id = Last_Id_Value 
FROM CMS59_ED.dbo.RSys_Last_Id 
WHERE Table_Name = 'Contact'

SELECT
    CAST(
    CONVERT(bigint, @Last_Id) + ROW_NUMBER() 
    OVER(ORDER BY CustomerID) 
    AS Binary(8)) AS Contact_Id,
    *
FROM SalesLT.Customer
Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s


%d bloggers like this: