LISTING 2: Code That Appends an IDENTITY Column to a Temporary Table

USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'employees')
DROP TABLE employees
GO
-- Note use the command below in SQL Server 2000.
-- Only use sp_dboption ‘pubs’,’select into/bulkcopy’,’true’ in
-- SQL Server 7.0.
ALTER DATABASE pubs SET RECOVERY BULK_LOGGED
GO
SELECT emp_id AS emp_num,
fname AS first,
minit AS middle,
lname AS last,
-- Note: Use an IDENTITY clause to add an incrementing value.
IDENTITY(int, 1, 1) AS job_num,
job_lvl AS job_level,
pub_id,
hire_date
INTO employees
FROM employee
GO
USE pubs
-- Note: Use the command below in SQL Server 2000.
-- Only use sp_dboption ‘pubs’,’select into/bulkcopy’,’false’ in SQL Server 7.0
ALTER DATABASE pubs SET RECOVERY FULL