USE [colombo]
GO
-- [1] table with IDENTITY column
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[mail_list1]')
AND type in (N'U'))
DROP TABLE [dbo].[mail_list1]
GO
CREATE TABLE [dbo].[mail_list1]
(
[n] INT IDENTITY(1,1) NOT NULL,
FirstName NVarchar (50) NULL,
LastName NVarchar (50) NULL,
) ON [PRIMARY];
GO
INSERT INTO dbo.mail_list1 (FirstName, LastName)
SELECT FirstName, LastName
FROM dbo.Employees
ORDER BY LastName, FirstName
GO
-- [2] select with ROW_NUMBER() saved to table
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[mail_list2]')
AND type in (N'U'))
DROP TABLE [dbo].[mail_list2]
GO
SELECT ROW_NUMBER() OVER
(ORDER BY EmpNo) AS n, FirstName, LastName
INTO mail_list2
FROM dbo.Employees
ORDER BY LastName, FirstName
GO
|