DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to remap keys with sequential update?

Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate how to remap a non-sequential key (column) to sequential numbering.

/* SQL Keys quick explanation
   With reference to the following tables
   PRIMARY KEYs: ProductID, ProductPhotoID - unique row identifier
   FOREIGN KEY: ProductPhoto(ProductID) - provides valid JOIN link to PK table
   UNIQUE KEY: ProductName - prevents duplicates
*/
CREATE TABLE Product(
  ProductID int identity(1,1) PRIMARY KEY, -- Clustered Unique Index created
  ProductName nvarchar(64) not null UNIQUE, -- Non-Clustered Unique Index created
  ModifiedDate date default(getdate()));
 
CREATE TABLE ProductPhoto (
  ProductPhotoID int identity(1,1) PRIMARY KEY, -- Clustered Unique Index created
  ProductID int REFERENCES Product(ProductID),
  LargePhoto varbinary(max),
  ModifiedDate date default(getdate()));

 

-- SQL sequential numbering - SQL sequential update

-- SQL map non-sequential numbers to sequential numbers

-- SQL map id with gaps to sequential id - SQL select into create table

/***** NOTE *****

Remapping Primary Keys requires remapping Foreign Keys also

*/

USE tempdb;

SELECT *

INTO   SODetail

FROM   AdventureWorks2008.Sales.SalesOrderDetail

GO

-- (121317 row(s) affected)

 

 

SELECT   TOP 100 *

FROM     SODetail

ORDER BY SalesOrderID,

         SalesOrderDetailID

/* Partial results

 

SalesOrderID      SalesOrderDetailID      CarrierTrackingNumber

43659 1     4911-403C-98

43659 2     4911-403C-98

43659 3     4911-403C-98

43659 4     4911-403C-98

43659 5     4911-403C-98

43659 6     4911-403C-98

43659 7     4911-403C-98

43659 8     4911-403C-98

43659 9     4911-403C-98

43659 10    4911-403C-98

43659 11    4911-403C-98

43659 12    4911-403C-98

43660 13    6431-4D57-83

43660 14    6431-4D57-83

43661 15    4E0A-4F89-AE

43661 16    4E0A-4F89-AE

43661 17    4E0A-4F89-AE

43661 18    4E0A-4F89-AE

43661 19    4E0A-4F89-AE

43661 20    4E0A-4F89-AE

43661 21    4E0A-4F89-AE

43661 22    4E0A-4F89-AE

43661 23    4E0A-4F89-AE

43661 24    4E0A-4F89-AE

43661 25    4E0A-4F89-AE

43661 26    4E0A-4F89-AE

43661 27    4E0A-4F89-AE

43661 28    4E0A-4F89-AE

43661 29    4E0A-4F89-AE

43662 30    2E53-4802-85

43662 31    2E53-4802-85

43662 32    2E53-4802-85

43662 33    2E53-4802-85

43662 34    2E53-4802-85

43662 35    2E53-4802-85

43662 36    2E53-4802-85

43662 37    2E53-4802-85

43662 38    2E53-4802-85

43662 39    2E53-4802-85

43662 40    2E53-4802-85

43662 41    2E53-4802-85

43662 42    2E53-4802-85

43662 43    2E53-4802-85

43662 44    2E53-4802-85

43662 45    2E53-4802-85

43662 46    2E53-4802-85

43662 47    2E53-4802-85

43662 48    2E53-4802-85

43662 49    2E53-4802-85

43662 50    2E53-4802-85

43662 51    2E53-4802-85

43663 52    1E90-4FBF-B6

43664 53    2F44-4BA1-BB

 

*/

 

-- SQL Common Table Expression - CTE

-- T-SQL identity function

;

WITH cteDistinctSO

     AS (SELECT DISTINCT SalesOrderID

         FROM   SODetail)

SELECT   [SequentialID] = Identity(INT,1,1),

         SalesOrderID

INTO     #SequentialMapping

FROM     cteDistinctSO

ORDER BY SalesOrderID ASC

GO

--(31465 row(s) affected)

 

SELECT   TOP 10 *

FROM     #SequentialMapping

ORDER BY SalesOrderID

GO

 

/* 

 

SequentialID      SalesOrderID

1     43659

2     43660

3     43661

4     43662

5     43663

6     43664

7     43665

8     43666

9     43667

10    43668

*/

 

-- SQL Server sequential update

UPDATE s

SET    s.SalesOrderID = m.[SequentialID]

FROM   SODetail s

       INNER JOIN #SequentialMapping m

         ON s.SalesOrderID = m.SalesOrderID

 

GO

 

SELECT   TOP 100 *

FROM     SODetail

ORDER BY SalesOrderID,

         SalesOrderDetailID

/* Partial results

 

SalesOrderID      SalesOrderDetailID      CarrierTrackingNumber

1     1     4911-403C-98

1     2     4911-403C-98

1     3     4911-403C-98

1     4     4911-403C-98

1     5     4911-403C-98

1     6     4911-403C-98

1     7     4911-403C-98

1     8     4911-403C-98

1     9     4911-403C-98

1     10    4911-403C-98

1     11    4911-403C-98

1     12    4911-403C-98

2     13    6431-4D57-83

2     14    6431-4D57-83

3     15    4E0A-4F89-AE

3     16    4E0A-4F89-AE

3     17    4E0A-4F89-AE

3     18    4E0A-4F89-AE

3     19    4E0A-4F89-AE

3     20    4E0A-4F89-AE

3     21    4E0A-4F89-AE

3     22    4E0A-4F89-AE

3     23    4E0A-4F89-AE

3     24    4E0A-4F89-AE

3     25    4E0A-4F89-AE

3     26    4E0A-4F89-AE

3     27    4E0A-4F89-AE

3     28    4E0A-4F89-AE

3     29    4E0A-4F89-AE

4     30    2E53-4802-85

4     31    2E53-4802-85

4     32    2E53-4802-85

4     33    2E53-4802-85

4     34    2E53-4802-85

4     35    2E53-4802-85

4     36    2E53-4802-85

4     37    2E53-4802-85

4     38    2E53-4802-85

4     39    2E53-4802-85

4     40    2E53-4802-85

4     41    2E53-4802-85

4     42    2E53-4802-85

4     43    2E53-4802-85

4     44    2E53-4802-85

4     45    2E53-4802-85

4     46    2E53-4802-85

4     47    2E53-4802-85

4     48    2E53-4802-85

4     49    2E53-4802-85

4     50    2E53-4802-85

4     51    2E53-4802-85

5     52    1E90-4FBF-B6

6     53    2F44-4BA1-BB

 

*/

-- Cleanup

DROP TABLE tempdb.dbo.SODetail

DROP TABLE #SequentialMapping

GO

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE