数据库游标导入数据

 我们平时经常会遇到导入数据的问题，以下是我个人的方法，写游标导入数据，方便快捷而且还可以对数据进行一系列的处理

 

declare FeeInsertStudent cursor for
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT 
      [LoginName]
      ,[CName]
      ,[EName]
      ,[Birthday]
      ,case when [Sex]='女' then 1 else 0 end as sex
      ,[MobileNum]
      ,[HomeAddress]
      ,[Email]
      ,case [UserType] when '获奖学生' then '001' when '推荐学生' then '002' when '课题申请' then '003' else '004' end stutype
      ,[Specialty]
      ,[AwardInfo]
      ,[CourseInfo]
      , case [UserFor] when '区县分院小研究员' then '2' when '市级小研究员' then '3' else '1' end StuLevel
      ,[School] 
      ,[SchoolAddress]
      ,[SchoolTel]
      ,case [AreaName] when '普陀区' then '0006' 
      when '长宁区' then '0005' when '宝山区' then '0011' when '奉贤区' then '0017' 
      when '黄浦区' then '0001' when '崇明县' then '0018' when '卢湾区' then '0002' 
      when '闸北区' then '0007' when '静安区' then '0004' when '青浦区' then '0015' 
      when '杨浦区' then '0009' when '闵行区' then '0010' when '南汇区' then '0016'
      when '金山区' then '0013' when '虹口区' then '0008'
       when '浦东新区' then '0019' when '徐汇区' then '0003'
        when '嘉定区' then '0012' when '松江区' then '0014' else '0020' end District
      ,[Teacher]
      ,[TeacherTel]
      ,[ayear]
  FROM [ZZ_MIFVS].[dbo].[Student]
--打开游标 
open FeeInsertStudent
 --定义所需要的变量
declare @LoginName nvarchar(50)
declare @StuName nvarchar(50)
declare @EnglishName nvarchar(50)
declare @Sex int  
declare @District nvarchar(50)  
declare @HomeAddress nvarchar(500)  
declare @BirthDay nvarchar(50)  
declare @Phone nvarchar(50)  
declare @Email nvarchar(200)  
declare @School nvarchar(50)  
declare @SchoolPhone nvarchar(50)   
declare @SchoolTeacher nvarchar(50) 
declare @Teacher nvarchar(50)   
declare @TeacherPhone nvarchar(50)  
declare @SchoolAddress nvarchar(500)  
declare @CourseInfo nchar(50)  
declare @Awarding nvarchar(MAX)  
declare @StuType nvarchar(50)  
declare @Interest nvarchar(MAX)
declare @StuLevel nvarchar(50)  
declare @YearTerm nvarchar(50)  
 
 
--从游标里取出数据给 变量 赋值
fetch next from FeeInsertStudent into @LoginName,@StuName,@EnglishName,@BirthDay,@Sex,@Phone,@HomeAddress,@Email,@StuType
,@Interest,@Awarding,@CourseInfo,@StuLevel,@School,@SchoolAddress,@SchoolPhone,@District,@Teacher,@TeacherPhone,@YearTerm
while @@FETCH_STATUS=0
begin
insert into QSN_StuInfo(StuID,LoginName,StuName,EnglishName,BirthDay,Sex,YearTerm,StuType,StuLevel,District,
Interest,Awarding,Email,Phone,School,SchoolAddress,SchoolPhone,SchoolTeacher,TeacherPhone,HomeAddress)
values( newid(),@LoginName,@StuName,@EnglishName,@BirthDay,@Sex,@YearTerm,@StuType,@StuLevel,@District,
@Interest,@Awarding,@Email,@Phone,@School,@SchoolAddress,@SchoolPhone,@SchoolTeacher,@TeacherPhone,@HomeAddress)
 
--从游标里取出数据给 变量 赋值
fetch next from FeeInsertStudent into @LoginName,@StuName,@EnglishName,@BirthDay,@Sex,@Phone,@HomeAddress,@Email,@StuType
,@Interest,@Awarding,@CourseInfo,@StuLevel,@School,@SchoolAddress,@SchoolPhone,@District,@Teacher,@TeacherPhone,@YearTerm
end
close FeeInsertStudent         --关闭游标
deallocate FeeInsertStudent                 --撤销游标

 

 

 

//类型转换

CONVERT(varchar(100), CAST(@Tel AS decimal(38,0)))