合并两个动态字段表面的数据
2009-09-26 11:47 苏飞 阅读(695) 评论(2) 编辑 收藏 举报阅读全文:http://www.sufeinet.com/thread-127-1-1.html
分割的函数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create FUNCTION [dbo].[SpitString]
(
@string nvarchar(500),--被分的字符串
@sp nvarchar(100) --分隔符
)
RETURNS
@_strings TABLE
(
id int,
string nvarchar(64),
inx int,
str1 nvarchar(65)
)
AS
BEGIN
declare @count int --计数
set @count=0
declare @index int
declare @one nvarchar(64)--取下来的一节
set @index=Charindex(@sp,@string)
while(@index>0)
begin
set @one=left(@string,@index-1)
set @count=@count+1
insert into @_strings (id,string,inx,str1) values(@count,@one,@index,@string)
set @string=right(@string,len(@string)-@index)
set @index=Charindex(@sp,@string)
end
insert into @_strings (id,string,inx,str1) values(@count+1,@string,@index,@one)
RETURN
END
go
--SELECT * FROM [dbo].[SpitString] ('asdads,ad,sadsa,d,w,dsa,dsa,d,sad,sa,' ,',')
(
@string nvarchar(500),--被分的字符串
@sp nvarchar(100) --分隔符
)
RETURNS
@_strings TABLE
(
id int,
string nvarchar(64),
inx int,
str1 nvarchar(65)
)
AS
BEGIN
declare @count int --计数
set @count=0
declare @index int
declare @one nvarchar(64)--取下来的一节
set @index=Charindex(@sp,@string)
while(@index>0)
begin
set @one=left(@string,@index-1)
set @count=@count+1
insert into @_strings (id,string,inx,str1) values(@count,@one,@index,@string)
set @string=right(@string,len(@string)-@index)
set @index=Charindex(@sp,@string)
end
insert into @_strings (id,string,inx,str1) values(@count+1,@string,@index,@one)
RETURN
END
go
--SELECT * FROM [dbo].[SpitString] ('asdads,ad,sadsa,d,w,dsa,dsa,d,sad,sa,' ,',')
存储过程
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,sufei>
-- Create date: <Create Date,,200909025>
-- Description: <合并表dbo.GameFieldMessages和dbo.FieldValues表的数据>
-- =============================================
ALTER PROCEDURE [dbo].[G_GameFieldMessagesToFieldValues]
@GNID int
AS
BEGIN
begin tran
declare @F int ;
set @F=1;--表示事务执行成功
--取出来数据库里的值
declare @strCell varchar(8000);
set @strCell='GNId '
declare @strFiled varchar(8000);
select @strFiled=FID from dbo.GameFieldMessages where GFMID=@GNID
set @F=@F+@@error;
declare @i int ; --变义变量@i
SELECT @i=count(*) FROM [dbo].[SpitString] (@strFiled ,',');
declare @j int; --变义变量
set @j=2;
declare @cluid varchar(500);
set @cluid='';
--先清理一下表
--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[newFeledMessages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
--drop table [dbo].[newFeledMessages]
declare @tablename varchar(100);
set @tablename='a'+Replace(Replace(CONVERT(VARCHAR(30),GETDATE(),9),' ','a'),':','b');
print @tablename
declare @strTable varchar(8000);
set @strTable='CREATE TABLE '+@tablename+'(
[GFMID] [int] IDENTITY(1,1) NOT NULL,
[GNId] [int] NULL,
CONSTRAINT PK_newFeledMessages'+@tablename+' PRIMARY KEY CLUSTERED
(
[GFMID] ASC
) ON [PRIMARY]
) ON [PRIMARY]'
exec(@strTable)
set @F=@F+@@error;
--给新表添加字段
while @j <= @i
begin
SELECT @cluid=string FROM [dbo].[SpitString] (@strFiled ,',') where id=@j
--print @cluid
--行到列的ID
declare @clu varchar(300);
set @clu='';
--得到列的名称
select @clu=FName from dbo.FieldInFo where FID=@cluid
--加新列
exec ('alter table '+@tablename+' add '+@clu+' varchar(50) null default null with values')
set @F=@F+@@error;
set @strCell=@strCell+','+@clu;
set @j = @j + 1
end
-- print @strCell
---现在应该添数据了
declare @strvalues varchar(8000);
Declare Mycursor cursor for select Vaules from dbo.FieldValues where GFMID=@GNID
Open Mycursor --打开游标
Fetch next from Mycursor into @strvalues --开始抓数据
while @@FETCH_STATUS = 0
begin
--select @strvalues=Vaules from dbo.FieldValues where GFMID=@GNID
declare @t int ; --变义变量
SELECT @t=count(*) FROM [dbo].[SpitString] (@strvalues ,',');
declare @k int; --变义变量
set @k=2;
declare @clu1 varchar(300);
set @clu1='';
declare @strTalbeValues varchar(8000);
set @strTalbeValues=N''''+str(@GNID);
-- 给新表添加字段
while @k <= @t
begin
SELECT @clu1=string FROM [dbo].[SpitString] (@strvalues ,',') where id=@k
set @strTalbeValues=@strTalbeValues+N''','''+@clu1;
-- 加新列
set @k = @k + 1
end
print @strTalbeValues
exec (N'insert into '+@tablename+'('+@strCell+N')values('+@strTalbeValues+N''')')
set @F=@F+@@error;
Fetch next from Mycursor into @strvalues
end
Close Mycursor --关闭游标
Deallocate Mycursor --删除游标
--查询看的内容
set @strTable ='select * from '+@tablename
exec(@strTable)
set @F=@F+@@error;
--删除表
set @strTable='drop table '+@tablename
exec(@strTable)
set @F=@F+@@error;
--事务处理
if(@F>0)
begin
commit tran;
end
else
begin
rollback tran;
end
end
--exec G_GameFieldMessagesToFieldValues 26
本人的博客不再维护从2013年就不再维护了
需要我帮助的朋友请到我的个人论坛 http://www.sufeinet.com 进行讨论,感谢大家对我的支持!