在开一贴：同样的问题--从Sql Server 数据库中 A表插入的B表
--
Insert into A(id,name)
select 'MaxID ',Name from B
--关键是这个 'MaxID ' 不在B表中，我是用这个存储过程 计算的ID
-----------------------
ALTER PROCEDURE dbo.alloc_id
@tbnameVARCHAR(32)
AS
BEGIN
BEGIN TRANSACTION
UPDATE table_alloc_id SET id = id + 1 WHERE tablename = @tbname
IF @@rowcount = 0
BEGIN
INSERT INTO table_alloc_id VALUES(@tbname, 1)
END
SELECT id FROM table_alloc_id WHERE tablename = @tbname
COMMIT TRANSACTION
END
------------------------------
比如B表中有50条数据，那么上面这个alloc_id 存储过程就应该循环计算id，并插入到A表中？
请问该怎么写SQL 语句？
[解决办法]
declare @name
declare my_cursor cursor scroll dynamic
for
SELECT name
FROM B
open my_cursor--打开游标
fetch next from my_cursor into @name
while(@@fetch_status=0)
begin
exec alloc_id 'B ',@this_id output--存储过程，用来生成表id
Insert into A(id,name)
select @this_id,name
from B
where current of my_cursor
fetch next from my_cursor into @name
end
close my_cursor--关闭游标
DEALLOCATE my_cursor--删除游标
