|
从数据库里面逐行逐行取出来，然后再依次进行update，或者修改操作，这时候需要游标
SET NOCOUNT ON
USE MB
GO
--定义变量
DECLARE @Enterprise_ID??? BigInt,
??? @Contact_ID???? BigInt,
??? @Bilateral_ID??? INT,
??? @Activity_ID??? INT,
??? @Activity_Code??? Char(36),
??? @Next_Call_Date??? datetime,
????? @VIN????? VarChar(30),
??? @Contract_date??? VarChar(30),
??? @Account_Number????? VarChar(30),
??? @Purchase_date??? VarChar(30),
??? @Model????? VarChar(30),
??? @Branch_Number????????? VarChar(30),
??? @NewOrUsed???? VarChar(30),
??? @Mature_date??? VarChar(30),
??? @Dealer_Number????????? VarChar(30),
??? @Term_Number??? VarChar(20),
??? @Last_Update_By??? VarChar(20)
SET @Activity_ID = 553??? -- 553 --Production Activity_ID
SET @Last_Update_By = 'Napoleon'
SET @Next_Call_Date = Convert(DateTime,Convert(VarChar(10),DateAdd(Day,1,GetDate()),121) + ' 9:00:00')
---声明cursor
DECLARE tempInput CURSOR
FOR
SELECT
?? c.Bilateral_ID,
?? c.Enterprise_ID,
?? c.Contact_ID,
?? VIN = LEFT(t.VIN,20),
?? t.Purchase_Date,
?? t.Decoded_Model,
?? NewOrUsed =
?? case
??? when t.Number_Owners ='1' then 'New'
??? else 'Used'
?? end,
?? t.Maturity_Date,
?? t.Selling_Dealer_Code,
?? Term =CONVERT(VarChar(20),dv.TERM)
FROM dragnet_5064_ticket_7561_20081103 t
left join napoleon_report..data_vehicles dv on
?? t.vin = dv.vin
left join mb..Contacts_DealerContacts c on
?? dv.owner_contact_id = c.contact_id
--打开游标??
--取出了游标里的数据，然后把取出来的数据放到变量里面
--//从游标里取出数据赋值到我们刚才声明的2个变量中
Open tempInput
FETCH NEXT FROM tempInput INTO @Bilateral_ID,@Enterprise_ID,@Contact_ID,@VIN,@Purchase_date,@Model,@NewOrUsed,@Mature_date,@Dealer_Number,@Term_Number
//判断游标的状态
//0 fetch语句成功
//-1 fetch语句失败或此行不在结果集中
//-2被提取的行不存在
--然后把变量的值给 插入到所需要的表中
While (@@FETCH_STATUS <> -1)（一个while相当与一个for循环）
BEGIN （begin对应与一个end）
?? SET @Activity_Code = CONVERT(Char(36),NEWID())
?? INSERT INTO act_item (Buying_Center_ID,Enterprise_ID,Contact_ID,Activity_ID,Activity_date,Activity_Code,Closed,Closed_Reason,Next_Call_Date,Next_Call_Time,Telemarketers,Bilateral_ID,rowguid,Last_Update_Date,Last_Update_By,created_by)
?? VALUES (1,@Activity_ID,getdate(),@Activity_Code,NULL,@Next_Call_Date,@Bilateral_ID,NEWID(),GetDate(),@Last_Update_By,@Last_Update_By)
?? INSERT INTO actd_dfo (Activity_Code,Row_IDName,Data)
?? VALUES (@Activity_Code,'VIN',@VIN)
??
?? INSERT INTO actd_dfo (Activity_Code,'Purchase Date',@Purchase_date)
?? INSERT INTO actd_dfo (Activity_Code,'Model',@Model)
??
?? INSERT INTO actd_dfo (Activity_Code,'New/Used',@NewOrUsed)
?? INSERT INTO actd_dfo (Activity_Code,'Maturity Date',@Mature_date)
??
?? INSERT INTO actd_dfo (Activity_Code,'Dealer Number',@Dealer_Number)
?? INSERT INTO actd_dfo (Activity_Code,'Term Number',@Term_Number)
--END（在处理程序的时候就是这个地方写错了，让我们那帮头找了很久，因为这句，他就提起end了,游标不会在往下面走了，陷入了死循环，end写在后面那个地方）
--用游标去取下一条记录
FETCH NEXT FROM tempInput INTO @Bilateral_ID,@Term_Number END
--//关闭游标
CLOSE tempInput
--//撤销游标DEALLOCATE tempInput
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
