SQL2008将b表数据插入c表实例

我现在有个b表，表结构如下(b2中A代表10，B代表11，C代表12)： b1 b2   
1 1,2,3,4,5,6,7,8,9,A,B,C   
    
然后我创建另外一个表c，想将b表中b1和b2数据插入进去，表结构如下：
c1 c2
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
1 11
1 12

.......

/*算法：

  1.通过游标将b表中每行数据取出来

  2.然后用WHILE语句循环提取b表中b2的值，并插入到c表中。*/

基础数据源码：

IF OBJECT_ID('b') IS NOT NULL
DROP TABLE b;
GO
IF OBJECT_ID('c') IS NOT NULL 
DROP TABLE a;
GO
--创建b表
CREATE TABLE b
( b1 INT , --产品ID
  b2 VARCHAR(50) --产品合同月份
 );
--插入数据
INSERT INTO b VALUES (1,'1,2,3,4,5,6,7,8,9,A,B,C');
INSERT INTO b VALUES (3,'1,3,5,7,9,B',6);

GO
--创建c表
CREATE TABLE c
(c1 INT,--产品ID
 c2 INT --产品月份);

存储过程源码：

IF OBJECT_ID('spInsc') IS NOT NULL
DROP PROCEDURE spInsc;
GO
CREATE PROCEDURE spInsc
AS 
BEGIN
	DECLARE @m CHAR(5),--b2中提取的字符串
	        @b1 INT ,
	        @b2 VARCHAR(50),--产品合同月份
	        @n INT,--b2中字符串间隔
	        @nc INT --b2中字符串的数量  
	DECLARE cur CURSOR FOR SELECT DISTINCT b1,b2 FROM b;
	OPEN cur;
	FETCH NEXT FROM cur INTO @b1,@b2;
	WHILE @@FETCH_STATUS=0
	  BEGIN
	    /*查找b2中字符串的数量*/
	  	SELECT @nc=LEN(b2) FROM b WHERE b1=@b1;	
			 SET @n=1;
			 WHILE @n<=@nc
			   BEGIN
	       		 SET @m=SUBSTRING(@b2,@n,1);
	       		 INSERT INTO c 
	       		 VALUES (@b1,
	       		         CASE @m
	       		           WHEN 'A' THEN '10'
	       		           WHEN 'B' THEN '11'
	       		           WHEN 'C' THEN '12'
	       		         ELSE @m END);	
	       		 SET @n=@n+2;      	       
			   END; 		  
		FETCH NEXT FROM cur INTO @b1,@b2;	
	  END;    		  
	CLOSE cur;
	DEALLOCATE cur;  
END;

存储过程源码2：

Create PROCEDURE [dbo].[InsertToTablec] 

AS
begin tran
declare @error int
set @error=0
declare T1 cursor for select b1,b2 from dbo.b
declare @b1 int,@b2 varchar(50)
open T1
fetch next from T1 into @b1,@b2
while @@FETCH_STATUS=0
begin
	declare  @id  varchar(300),@m  int,@n  int  ,@count int
	set  @m=CHARINDEX(',',@b2)  
	set  @n=1  
	set @count=0
	WHILE  @m>0
	BEGIN  
		   set  @id=substring(@b2,@n,@m-@n)  
		   
		   insert into c (c1,c2) values ( @b1,case @id  when 'A' then '10' when 'B' then '11' when 'C' then '12' else @id end)
		 
		   
	       set @error=@error+@@ERROR
		   set  @n=@m+1  
		   set  @m=CHARINDEX(',',@b2,@n)  
	END
	
	if(@n<LEN(@b2)+1)
	begin
		set @id=SUBSTRING(@b2,@n,LEN(@b2)-@n+1)
	 
	insert into c (c1,c2) values ( @b1,case @id  when 'A' then '10' when 'B' then '11' when 'C' then '12' else @id end)
		 
	end
	

	fetch next from T1 into  @b1,@b2
end
close T1
deallocate T1

--提交
if @error=0
begin
	commit tran
end
else
begin
	rollback tran
end

两种存储过程都可以实现这个功能。