insert into t1 (number,name)
(select name from employee )
我的number是从123到223的数字
name共有100条,该怎么写啊1
使得插入的数据的number从123开始到223结束?
高手指点一下啊!
(select name from employee )
我的number是从123到223的数字
name共有100条,该怎么写啊1
使得插入的数据的number从123开始到223结束?
高手指点一下啊!
解决方案 »
- 格式化SQL 脚本
- sql 导入excel文档的问题
- 如何把Ms-Sql数据库中照片,转变成jpg文件?
- 在保存数据库关系图时报“将截断字符串或二进制数据。 语句已终止。 sp_creatediagram过程试图返回状态 NULL,这是不允许的。将返回状态 0。
- 请教什么是查寻树?什么是最低效率查询树和优化后的查询树?
- 求助!SQL2008R2 远程连接问题
- 关于部门编号,很矛盾啊...高手释疑一下
- 如何读出当前记录的上一条记录和下一条记录
- ltrim和rtrim
- vb中怎么打印文本框的内容?
- 删除数据后,如何让自增长列的id值重新从1开始?
- 我要执行一存储过程,在我设定的时间范围内间隔3分钟执行一次
(select identity(123,1), name from employee )
go
insert into t1 (number,name)
select id,name from #t1
where id<=223
select identity(int,123,1) as number, name
into t1
from employee
set @number=123
while @number!>223
begin
insert into t1 (number,name)
(select @number,name from employee )
set @number=@number+1
endselect * from t1
insert into t1 select * from #t;
drop table #t;
原来表里number也有数据的,
现在是新增数据
declare @name varchar(8),@number int;declare cur cursor for select [name] from employee;
open cur;
fetch cur into @name;
set @number=123;
while @@fetch_status=0
begin
print @name
fetch cur into @name;
set @number=@number+1
insert into t1 (number,name) select @number,name from employee;
end
close cur;
deallocate cur;
declare @number int
set @number = 123
declare cur_tmp cursor for
select name from employee
open cur_tmp
fetch next from cur_tmp into @name
while @@fetch_status=0
begin
insert into t1 (number,name)
select @i,name from employee
set @i = @i + 1
fetch next from cur_tmp into @name
end
close cur_tmp
deallocate cur_tmp
用游标的:
declare @name varchar(8),@number int;declare cur cursor for select [name] from employee;
open cur;
fetch cur into @name;
set @number=123;
while @@fetch_status=0
begin
print @name
fetch cur into @name;
set @number=@number+1
insert into t1 (number,name) values(@number,name);
end
close cur;
deallocate cur;
用游标的:
declare @name varchar(8),@number int;declare cur cursor for select [name] from employee;
open cur;
fetch cur into @name;
set @number=123;
while @@fetch_status=0
begin
set @number=@number+1
insert into t1 (number,name) values(@number,name);
fetch cur into @name;
end
close cur;
deallocate cur;
open cur;
fetch cur into @name;
set @number=123;
while @@fetch_status=0
begin
insert into t1 (number,name) values(@number,name);
fetch cur into @name;
set @number=@number+1
end
close cur;
deallocate cur;
declare @number int
set @number = 123
declare cur_tmp cursor for
select name from employee
open cur_tmp
fetch next from cur_tmp into @name
while @@fetch_status=0
begin
insert into t1 (number,name)
select @number,@name
set @number = @number + 1
fetch next from cur_tmp into @name
end
close cur_tmp
deallocate cur_tmp
-----------------------------------------------------------------------------
游标好好学习学习~.......
这里不能用 identity的吧?
?
给我说?
我在查询分析器中执行,会报错的啊
select 中保存 identity的,需要有into
insert into t1 (name) select name from employee --再通过变量,修改number,从123开始
declare @i int
set @i=122
update a set @i=@i+1,number=@i from employee a
WHILE (SELECT number FROM t1) <= 223
BEGIN
INSERT INTO t1 (number,name)
SELECT IDentity(int,123,1) 'RowOrder',name
FROM employee
SELECT MAX(number) FROM t1
IF (SELECT MAX(number) FROM t1) > 223
BREAK
ELSE
CONTINUE
END
DECLARE @employeeid int
SET @employeeid = 1
WHILE (SELECT number FROM t1) <= 223
BEGIN
INSERT INTO t1 (number,name)
SELECT IDentity(int,123,1) 'RowOrder',name
FROM employee
WHERE employeeid=1
SELECT MAX(number) FROM t1
IF (SELECT MAX(number) FROM t1) > 223
BREAK
ELSE
SET @employeeid = employeeid + 1
CONTINUE
END
DECLARE @employeeid int
SET @employeeid = 1
WHILE (SELECT number FROM t1) <= 223
BEGIN
INSERT INTO t1 (number,name)
SELECT IDentity(int,123,1) 'RowOrder',name
FROM employee
WHERE employeeid = @employeeid
SELECT MAX(number) FROM t1
IF (SELECT MAX(number) FROM t1) > 223
BREAK
ELSE
SET @employeeid = employeeid + 1
CONTINUE
END
(select a=identity(int,123,1),name from employee where a<236)
where id<=223
goinsert into t1 (number,name) select id,name from #t1
drop table #t1
goinsert into t1 (number,name) select id,name from #t1
drop table #t1