sql的数据类弄杂谈

前一段时间由于碰到两个数据库的数据转移!

所以加深了对SQL的一个字符操作函数的了解,NTEXT,TEXT,IMAGE这些数据类型都不能使用一些通用的操作函数如left,right,trim 等

由于我要转移的数据的表有的使用了ntext的数据

在这个字段里有一些数据要更新!

所以用脚本语言asp来做一个中转!
我想说的是有这种情况最好要用脚本做一个中转


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROC SORT
@NEW VARCHAR(8),
@OLD varchar(8)
as
 
declare my_cursor cursor  dynamic /*scroll表示可随意移动游标指针（否则只能向前），dynamic表示可以读写游标（否则游标只读）*/
for
 select ecomsql.dbo.comwenzhanglei.id,ecomsql.dbo.comwenzhanglei.lei,ecomsql.dbo.comwenzhanglei.connect from ecomsql.dbo.comwenzhanglei where ecomsql.dbo.comwenzhanglei.comid=@new
open my_cursor
declare @name char(100),@tempid int,@conn int

fetch next from my_cursor into @tempid,@name,@conn
while(@@fetch_status=0)
  begin
 declare @data varchar(10)
 set @data=(select ecom.dbo.comwenzhanglei.id  from ecom.dbo.comwenzhanglei where ecom.dbo.comwenzhanglei.comid=@old and ecom.dbo.comwenzhanglei.lei=@name)
 if @data<>‘‘
   begin
    update ecomsql.dbo.comwenzhang set ecomsql.dbo.comwenzhang.lanid=@tempid where ecomsql.dbo.comwenzhang.comid=@new and ecomsql.dbo.comwenzhang.lanid=@data
   end
 if @conn<>0
   begin
  declare @tname varchar(20),@tid int
  set @tname=(select ecom.dbo.comwenzhanglei.lei from ecom.dbo.comwenzhanglei where ecom.dbo.comwenzhanglei.id=@conn and ecom.dbo.comwenzhanglei.comid=@old)
  set @tid=(select ecomsql.dbo.comwenzhanglei.id from ecomsql.dbo.comwenzhanglei where ecomsql.dbo.comwenzhanglei.lei=@tname and ecomsql.dbo.comwenzhanglei.comid=@new)
  update ecomsql.dbo.comwenzhanglei set ecomsql.dbo.comwenzhanglei.connect=@tid where ecomsql.dbo.comwenzhanglei.id=@tempid and ecomsql.dbo.comwenzhanglei.comid=@new
   end

    fetch next from my_cursor into @tempid,@name,@conn
  end
--fetch first from my_cursor into @name
close my_cursor
deallocate my_cursor

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO