--TRY:create table #Utable (tid int identity(1,1),tname nvarchar(100),tqty int) insert into #utable (tname,tqty) select 'a',12 union select 'b',13 union select 'c',14select * from #utabledrop table #utable
--- example in sql helpSET NOCOUNT ONDECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40), @message varchar(80), @title varchar(80)PRINT "-------- Utah Authors report --------"DECLARE authors_cursor CURSOR FOR SELECT au_id, au_fname, au_lname FROM authors WHERE state = "UT" ORDER BY au_idOPEN authors_cursorFETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lnameWHILE @@FETCH_STATUS = 0 BEGIN PRINT " " SELECT @message = "----- Books by Author: " + @au_fname + " " + @au_lname PRINT @message -- Declare an inner cursor based -- on au_id from the outer cursor. DECLARE titles_cursor CURSOR FOR SELECT t.title FROM titleauthor ta, titles t WHERE ta.title_id = t.title_id AND ta.au_id = @au_id -- Variable value from the outer cursor OPEN titles_cursor FETCH NEXT FROM titles_cursor INTO @title IF @@FETCH_STATUS <> 0 PRINT " <<No Books>>" WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @message = " " + @title PRINT @message FETCH NEXT FROM titles_cursor INTO @title
END CLOSE titles_cursor DEALLOCATE titles_cursor
-- Get the next author. FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lname ENDCLOSE authors_cursor DEALLOCATE authors_cursor
简单一点!declare cursor_insert cursor for select c# from cs declare @i int open cursor_insert fetch cursor_insert into @i while @@fetch_status=0 begin print @i fetch cursor_insert into @i end close cursor_insert deallocate cursor_insert
declare dali_cursor scroll cursor for------------定义游标 select * from jobs where max_lvl>100 ----------游标体查找语句 open dali_cursor-----------------打开游标 -------fetch next from dali_cursor------------提取游标-----next---last close dali_cursor------------------关闭游标 deallocate dali_cursor------------释放游标
那你就不要用identity 改为insert 表 (id,name) select isnull(max(id),0)+1,@参数 from 表插入数据!
insert into #utable
(tname,tqty)
select 'a',12
union
select 'b',13
union
select 'c',14select * from #utabledrop table #utable
@message varchar(80), @title varchar(80)PRINT "-------- Utah Authors report --------"DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_idOPEN authors_cursorFETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lnameWHILE @@FETCH_STATUS = 0
BEGIN
PRINT " "
SELECT @message = "----- Books by Author: " +
@au_fname + " " + @au_lname PRINT @message -- Declare an inner cursor based
-- on au_id from the outer cursor. DECLARE titles_cursor CURSOR FOR
SELECT t.title
FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND
ta.au_id = @au_id -- Variable value from the outer cursor OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @title IF @@FETCH_STATUS <> 0
PRINT " <<No Books>>" WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = " " + @title
PRINT @message
FETCH NEXT FROM titles_cursor INTO @title
END CLOSE titles_cursor
DEALLOCATE titles_cursor
-- Get the next author.
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
ENDCLOSE authors_cursor
DEALLOCATE authors_cursor
declare
@i int
open cursor_insert
fetch cursor_insert into @i
while @@fetch_status=0
begin
print @i
fetch cursor_insert into @i
end
close cursor_insert
deallocate cursor_insert
以前的数据继续增加,我想是删除后,数据还是从1开始。
select * from jobs where max_lvl>100 ----------游标体查找语句
open dali_cursor-----------------打开游标 -------fetch next from dali_cursor------------提取游标-----next---last
close dali_cursor------------------关闭游标
deallocate dali_cursor------------释放游标
改为insert 表 (id,name) select isnull(max(id),0)+1,@参数 from 表插入数据!
insert的,from的,表是一个吗?
@参数?是怎么回事?
不好意思, 刚刚接触sqlserver
DBCC CHECKIDENT (yourtablename, RESEED, 0)
go从1开始
-------------------^^^^^^^^^^^^^^^^^^^^^^^^^^^查询自己的表最大的编号加一insert 表 (id,name) select isnull(max(id),0)+1,@参数 from 表
-----------------------------------------------^^^^你要插入的其他值呀!
select (select isnull(max(col_num),0)+1 from tb_Location),另一个表的列 from 另一个表 where 条件
上面的方法还是不行,所有产生的col_num =1 不过我已经找到解决方法了,
我把drop table
create table
insert into
写在过程里这样就解决IDENTITY(int, 1,1) 的问题了。
多谢楼上的几位了新年快乐