|
|
Suppose that we have table with column that serves as record numerator, but need
to be rebuild. We will examine two ways to create copy of original table and
renumerate records during the copy operation.
USE [colombo]
GO
-- create sample data
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID
(N'[dbo].[myItems]')
AND type in (N'U'))
DROP TABLE [dbo].[myItems]
GO
CREATE TABLE [dbo].[myItems](
[item_id] INT IDENTITY(1,1) NOT NULL,
[ItemName] Varchar (50) NULL,
) ON [PRIMARY]
GO
INSERT INTO myItems (ItemName)
VALUES ('item1'),('item2'),('item3'),('item4');
DELETE FROM myItems WHERE item_id=2;
-- see existing data
SELECT * FROM myItems;
|
Returned results will be like this:
| item_id | ItemName |
| 1 | item1 |
| 3 | item3 |
| 4 | item4 |
-- [1] Create New table with IDENTITY column
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID
(N'[dbo].[myItems_new]')
AND type in (N'U'))
DROP TABLE [dbo].[myItems_new]
GO
CREATE TABLE [dbo].[myItems_new](
[item_id] INT IDENTITY(1,1) NOT NULL,
[ItemName] Varchar (50) NULL,
) ON [PRIMARY]
GO
INSERT INTO dbo.myItems_new (ItemName)
SELECT ItemName
FROM dbo.myItems
ORDER BY ItemName
GO
-- [2] select with ROW_NUMBER() saved to new table
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID
(N'[dbo].[myItems_new]')
AND type in (N'U'))
DROP TABLE [dbo].[myItems_new]
GO
SELECT ROW_NUMBER()
OVER (ORDER BY ItemName) AS item_id, ItemName
INTO myItems_new
FROM dbo.myItems
ORDER BY ItemName
GO
-- drop source table and rename newlly created
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID
(N'[dbo].[myItems]')
AND type in (N'U'))
DROP TABLE [dbo].[myItems]
GO
EXEC sp_rename
'myItems_new',
'myItems'
GO
-- see results
SELECT * FROM myItems;
|
Returned results will be like this:
| item_id | ItemName |
| 1 | item1 |
| 2 | item3 |
| 3 | item4 |
|