一次数据库导出数据时产生的相关语句，包含替换字段、开关远程服务功能、游标功能等

　　A、替换字符串

　　update dbo.CZL_CompanyPic set  PicPath=(Replace(PicPath,'/UploadFiles/','/uploadfile/')) update dbo.CZL_CompanyInfo set  DesPic=(Replace(DesPic,'/UploadFiles/','/uploadfile/')),Blimage=(Replace(Blimage,'/UploadFiles/','/uploadfile/'))

select count(1) from dbo.CZL_User select count(1) from dbo.CZL_CompanyPic select count(1) from dbo.CZL_CompanyInfo

select top 20 * from dbo.CZL_CompanyInfo order by id desc

select top 20 * from dbo.CZL_User order by id desc select top 20 * from dbo.CZL_CompanyPic  order by id desc

select a.UserID,a.id,a.CompanyName,a.userID,(select top 1 PicPath from CZL_CompanyPic p where p.UserId=a.UserId) as Pic from CZL_CompanyInfo a inner join CZL_User u on a.UserId=u.Id where  CompanyName !='' and (select top 1 PicPath from CZL_CompanyPic p where p.UserId=a.UserId)!='' order by id desc

select top 20 * from CZL_CompanyInfo c inner join CZL_CompanyPic p on  p.UserId=c.UserId　　　

　　update dbo.UserInfo set IsShow=1 where CHARINDEX('吴中',TrueName)>0 or  CHARINDEX('吴中',Address)>0

select CHARINDEX('a','ajdsj')

　　B、不运行以下，访问远程　　数据库数据会有问题

　　exec sp_configure 'show advanced options',1   reconfigure   exec sp_configure 'Ad Hoc Distributed Queries',1   reconfigure  --开启

　　exec sp_configure 'Ad Hoc Distributed Queries',0   reconfigure   exec sp_configure 'show advanced options',0   reconfigure --关闭

　　C、以下是一个含游标的可以当做存储过程的语句，是一个很好的游标使用实例

declare @LoginName varchar(15), @Password varchar(50), @UserName nvarchar(20), @MemeberCode varchar(20),@IsFlag int, @Score int, @Logins int, @LastDate varchar(23),  @AddDate  varchar(23), @IsActive int, @UpdateDate varchar(23), @IP  varchar(23), @CompanyName nvarchar(50),@URL varchar(100),@CompDes varchar(4000),@PicPath varchar(100),@sex int,@Tel varchar(20),@Mobile varchar(20), @Fax varchar(20), @Email varchar(20), @Address nvarchar(100),@Product  varchar(100),@ID int; select @IsFlag=0,@Score=0, @Logins=0,@IsActive=0,@MemeberCode='1',@LastDate=getdate(),@AddDate=getdate(),@UpdateDate=getdate() declare auth_cur cursor for select [UserName],[Truename],[Sex],[CompanyName],[Email],[Tel],[Mobile],[Fax],[Address],[Homepage],[UpdateTime],[CreateIP],[Logo],[Product],[Introduce]  ,[CreateTime] from OPENROWSET('SQLOLEDB','192.168.2.242';'kodf';'000000',Testdb.dbo.CompanyInfo3); open auth_cur fetch next from auth_cur   into @LoginName,@UserName,@sex, @CompanyName,  @Email,  @Tel, @Mobile,@Fax, @Address,  @URL,     @UpdateDate,   @IP,     @PicPath,@Product, @CompDes,  @AddDate while (@@fetch_status=0)   begin  -- begin tran --开始执行事务       ---先添加dbo.CZL_User表,再添加dbo.CZL_CompanyPic表,再添加dbo.CZL_CompanyInfo表     ---须获得dbo.CZL_User表新加数据的id ，默认密码：e10adc3949ba59abbe56e057f20f883e，即123456 Insert into c962.dbo.CZL_User(  [LoginName],[Password],[UserName],[MemeberCode],[IsFlag],[Score],[Logins],[LastDate],[AddDate], [IsActive], [UpdateDate],[IP],[Email] ) VALUES (  @LoginName+'_new', @Password, @UserName, @MemeberCode, @IsFlag, @Score, @Logins, @LastDate,  @AddDate, @IsActive, @UpdateDate, @IP, @Email ); Select @ID =max(Id) from OPENROWSET('SQLOLEDB','*.92.156.*';'c962';'438dfDBUhsUHfg',c962.dbo.CZL_User);--新加的用户id Insert into OPENROWSET('SQLOLEDB','*.92.156.*';'c962';'438dfDBUhsUHfg',c962.dbo.CZL_CompanyPic)(  PicPath, AddTime,UserId, IsActive, UpdateTime, IP ) VALUES (  @PicPath,@AddDate,@ID,@IsActive,@UpdateDate,@IP );

Insert into OPENROWSET('SQLOLEDB','*.92.156.*';'c962';'438dfDBUhsUHfg',c962.dbo.CZL_CompanyInfo)(  UserId,CompanyName,URL, CompDes, DesPic,  LinkMan,   sex, Tel, Mobile, Fax,  Email,  Address, IsFlag, AddTime,UpdateDate, IP, Blimage,Product ) VALUES (  @ID,  @CompanyName,@URL,@CompDes,@PicPath,@UserName, @sex,@Tel,@Mobile, @Fax, @Email, @Address,@IsFlag,@AddDate,@UpdateDate,@IP,@PicPath,@Product ); --if @@error<>0 --判断如果两条语句有任何一条出现错误 --rollback tran ---开始执行事务的回滚，恢复的转账开始之前状态 --else --commit tran ---执行这个事务的操作     fetch next from auth_cur into @LoginName,@UserName,@sex, @CompanyName,  @Email,  @Tel, @Mobile,@Fax, @Address,  @URL,     @UpdateDate,   @IP,     @PicPath,@Product, @CompDes, @AddDate   end close auth_cur deallocate auth_cur