IF Not Exists (Select 1 From syscolumns Where id = object_id(N'TableName') and Columnproperty(id,Name,'Isidentity')=1)
Begin
Alter Table TableName Add ColumnName int identity(1,1) not Null
End
Begin
Alter Table TableName Add ColumnName int identity(1,1) not Null
End
select identity(int,1,1) as id,* into newtable from table1
例如
select Player from table1
得到的结果是:
username
Jordan
kobe
yaoming我想问这个结果添加一个编号,成为如下的表id username
1 Jordan
2 kobe
3 yaoming
SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable--(2)
SELECT ID_Num = IDENTITY(int, 1, 1)
INTO NewTable
FROM OldTable
下面的示例将来自 pubs 数据库中 employee 表的所有行都插入到名为 employees 的新表。使用 IDENTITY 函数在 employees 表中从 100 而不是 1 开始编标识号。USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'employees')
DROP TABLE employees
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'SELECT emp_id AS emp_num,
fname AS first,
minit AS middle,
lname AS last,
IDENTITY(smallint, 100, 1) AS job_num,
job_lvl AS job_level,
pub_id,
hire_date
INTO employees
FROM employee
GO
USE pubs
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'