合并两个动态字段表面的数据

yangying

导读部分
-------------------------------------------------------------------------------------------------------------
SQL Server 2005 学习笔记系列文章
http://www.sufeinet.com/forum.php?mod=viewthread&tid=26

分割的函数

[SQL] 纯文本查看 复制代码

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,' ,',')

存储过程

[SQL] 纯文本查看 复制代码

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 [url=mailto:GFMID=@GNID]GFMID=@GNID[/url]
  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 [url=mailto:]'+@tablename+'[/url](
   [GFMID] [int] IDENTITY(1,1) NOT NULL,
   [GNId] [int] NULL,
   CONSTRAINT [url=mailto:PK_newFeledMessages]PK_newFeledMessages'+@tablename+'[/url] 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 [url=mailto:id=@j]id=@j[/url]
   --print @cluid 
   --行到列的ID
     declare @clu varchar(300);
     set @clu='';
   --得到列的名称
   select @clu=FName from dbo.FieldInFo where [url=mailto:FID=@cluid]FID=@cluid[/url] 
   --加新列
   exec ('alter table  [url=mailto:]'+@tablename+'[/url] add [url=mailto:]'+@clu+'[/url] 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 [url=mailto:GFMID=@GNID]GFMID=@GNID[/url]
  Open Mycursor                                  --打开游标 
  Fetch next from Mycursor into @strvalues     --开始抓数据 
  while @@FETCH_STATUS = 0
    begin 
    --select @strvalues=Vaules  from dbo.FieldValues where [url=mailto:GFMID=@GNID]GFMID=@GNID[/url]
    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 [url=mailto:id=@k]id=@k[/url]
      set @strTalbeValues=@strTalbeValues+N''','''+@clu1;        
    --      加新列
      set @k = @k + 1
      end
    print @strTalbeValues
    exec (N'insert into [url=mailto:]'+@tablename+'('+@strCell+N')values('+@strTalbeValues+N''')'[/url])
    set @F=@F+@@error;
    Fetch next from Mycursor into @strvalues
    end
  Close Mycursor                         --关闭游标 
  Deallocate Mycursor                    --删除游标 
  --查询看的内容
  set @strTable ='select * from [url=mailto:]'+@tablename[/url]
  exec(@strTable)
  set @F=@F+@@error;
  --删除表
  set @strTable='drop table [url=mailto:]'+@tablename[/url]
  exec(@strTable)
  set @F=@F+@@error;
        --事务处理
  if(@F>0)
  begin
    commit tran;
  end
  else
  begin
  rollback tran;
  end
end
--exec G_GameFieldMessagesToFieldValues 26