Write a complicated job
--- Connect to the remote machine database in the same domain or in the same CIDR Block
Bytes ----------------------------------------------------------------------------------------------------------
Exec sp_addmediaserver 'srv _ lnk ', '', 'sqloledb', 'database name'
Exec sp_add1_srvlogin 'srv _ lnk ', 'false', null, 'username', 'Password'
Go
-- Create a query and delete a temporary table
Create Table # tempreportdata
(
Orderid int not null,
Adddate datetime null
)
Select * from # tempreportdata
Drop table # tempreportdata
-- Query
Bytes ----------------------------------------------------------------------------------------------------------
Select orderid, left (adddate, 10) from srv_lnk.benqshopnew.dbo.shop_returnorderid
Select * From srv_lnk.benqshopnew.dbo.shop_returnorderid_bak
-- Use a temporary table to save data
Bytes ----------------------------------------------------------------------------------------------------------
Insert into # tempreportdata (orderid, adddate) Select orderid, adddate from srv_lnk.benqshopnew.dbo.shop_returnorderid
Insert into srv_lnk.benqshopnew.dbo.shop_returnorderid (orderid, adddate) Select orderid, adddate from # tempreportdata
---- Get the proportion and currency conversion
Bytes ----------------------------------------------------------------------------------------------------------
Declare @ icpercent float -- industry customer segmentation
Declare @ sipercent float -- industry proxy Division
Declare @ moneytobb int -- currency conversion
Select @ icpercent = value from varshop. DBO. base_info where valuename = 'si' -- set the customer share ratio
Select @ sipercent = value from varshop. DBO. base_info where valuename = 'ic '-- set the proportion of customer proxy
Select @ moneytobb = value from varshop. DBO. base_info where valuename = 'moneytobb' -- set currency conversion
-- Cursor loop execution Logic
Bytes ----------------------------------------------------------------------------------------------------------
Declare @ crsrvar cursor -- declare the cursor
Declare @ porderid int -- Order Number
Set @ crsrvar = cursor for select orderid from srv_lnk.benqshopnew.dbo.shop_returnorderid
Open @ crsrvar
Fetch next from @ crsrvar into @ porderid
-- Perform logical processing and execution of stored procedures.
While (@ fetch_status = 0) -- unfortunately, T-SQL does not have the do while statement
Begin
Fetch next from @ crsrvar into @ porderid
-- Perform logical processing and execution of stored procedures.
End
Close @ crsrvar
Deallocate @ crsrvar
-- Delete the original table data after data transfer
Bytes ----------------------------------------------------------------------------------------------------------
Insert srv_lnk.benqshopnew.dbo.shop_returnorderid_bak (orderid, adddate) Select orderid, adddate from srv_lnk.benqshopnew.dbo.shop_returnorderid
Delete from srv_lnk.benqshopnew.dbo.shop_returnorderid
Generate a random numeric expression (RAND (checksum (newid () between 0 and 1. The advantage is that the random numbers in each row are different from those in created by jecray.