select *,identity(int,10,1) into table2 from table1
我想一条语句完成,不知可能否,因为其他地方用到这个结果,所以不能用drop
更正: select columA,IDENTITY(int,10,1) as columB into #Temp from testselect * from #Tempdrop table #Temp 你的columA没有次序,不能用一条语句。
agree with csdnm,otherwise u can get line number from one sentence.
如果sql语句中有order by 也不行吗, 另外在查询分析器 中结果集窗口中左侧的行号1,2,3....能不能获得
Examples This example inserts all rows from the employee table from the pubs database into a new table called employees. The IDENTITY function is used to start identification numbers at 100 instead of 1 in the employees table.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'
select columA,IDENTITY(int,10,1) as columB
into #Temp
from testselect * from #Tempdrop table #Temp
你的columA没有次序,不能用一条语句。
另外在查询分析器 中结果集窗口中左侧的行号1,2,3....能不能获得
This example inserts all rows from the employee table from the pubs database into a new table called employees. The IDENTITY function is used to start identification numbers at 100 instead of 1 in the employees table.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'
select *,identity(int,10,1) into table2 from table1
--->
select *,identity(int,10,1) as num into table2 from table1