34,576
社区成员
发帖
与我相关
我的任务
分享
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');
GO
--创建c表
CREATE TABLE c
(c1 INT,--产品ID
c2 INT --产品月份);
USE tempdb;
GO
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;
USE tempdb;
GO
/****** Object: StoredProcedure [dbo].[InsertToTablec] Script Date: 04/13/2012 11:19:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------------------------------------
--用途:
------------------------------------
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