从Sql Server 数据库中 A表插入的B表
--
Insert   into   A(id,name)
select       'MaxID ',Name     from     B  
--关键是这个   'MaxID '   不在B表中，我是用这个存储过程   计算的ID
-----------------------
ALTER     PROCEDURE   dbo.alloc_id
@tbname VARCHAR(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--删除游标


ALTER PROCEDURE dbo.alloc_id
@tbname varchar(32)
@get_id varchar(50) output
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 @get_id=max(id) FROM table_alloc_id WHERE tablename = @tbname
COMMIT TRANSACTION
END