从数据库里面逐行逐行取出来,然后再依次进行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, @Enterprise_ID, @Contact_ID, @Activity_ID, getdate(), @Activity_Code, 0, NULL, @Next_Call_Date, @Next_Call_Date, NULL, @Bilateral_ID, NEWID(), GetDate(), @Last_Update_By, @Last_Update_By)
INSERT INTO actd_dfo (Activity_Code, Activity_ID, Row_IDName, Data)
VALUES (@Activity_Code, @Activity_ID, 'VIN', @VIN)
INSERT INTO actd_dfo (Activity_Code, Activity_ID, Row_IDName, Data)
VALUES (@Activity_Code, @Activity_ID, 'Purchase Date', @Purchase_date)
INSERT INTO actd_dfo (Activity_Code, Activity_ID, Row_IDName, Data)
VALUES (@Activity_Code, @Activity_ID, 'Model', @Model)
INSERT INTO actd_dfo (Activity_Code, Activity_ID, Row_IDName, Data)
VALUES (@Activity_Code, @Activity_ID, 'New/Used', @NewOrUsed)
INSERT INTO actd_dfo (Activity_Code, Activity_ID, Row_IDName, Data)
VALUES (@Activity_Code, @Activity_ID, 'Maturity Date', @Mature_date)
INSERT INTO actd_dfo (Activity_Code, Activity_ID, Row_IDName, Data)
VALUES (@Activity_Code, @Activity_ID, 'Dealer Number', @Dealer_Number)
INSERT INTO actd_dfo (Activity_Code, Activity_ID, Row_IDName, Data)
VALUES (@Activity_Code, @Activity_ID, 'Term Number', @Term_Number)
--END(在处理程序的时候就是这个地方写错了,让我们那帮头找了很久,因为这句,他就提起end了,游标不会在往下面走了,陷入了死循环,end写在后面那个地方)
--用游标去取下一条记录
FETCH NEXT FROM tempInput INTO @Bilateral_ID,@Enterprise_ID,@Contact_ID,@VIN,@Purchase_date,@Model,@NewOrUsed,@Mature_date,@Dealer_Number,@Term_Number END
--//关闭游标
CLOSE tempInput
--//撤销游标
DEALLOCATE tempInput