October 14, 2011 at 6:43 am
Please can I get the best way to re srite this script using set based theory ...many thanks
CREATE TABLE #TRGT_FLTS
(
TRPT_IDINTEGER,
FLEET_IDINTEGER
)
CREATE TABLE #NEW_CLIENT_WDV
(
TRPT_IDINTEGER NOT NULL,
FLEET_IDINTEGER NOT NULL,
CONTRACT_TYPE_IDINTEGER NOT NULL,
CLIENT_WDV_IDINTEGER,
MANUFACTURER_IDINTEGER NULL,
MODEL_IDINTEGER NULL,
ADJUSTMENTDECIMAL(5, 4) NULL,
[EXPIRY_DATE]DATETIME NULL,
VARIANT_IDINTEGER NULL
)
INSERT INTO #TRGT_FLTS
TRPT_ID
VALUES
(900081568),
(900084379),
(900081901),
(900067557),
(900062801),
(900064621),
(900058580),
(900058043),
(900055005),
(900054872),
(900054604),
(900053913),
(900053892),
(900053887),
(900053080),
(900053020),
(900052996),
(900052876),
(900052270),
(900051613),
(900051355),
(900051174),
(900051160),
(900051159),
(900051143),
(900051118),
(900051082),
(900051080),
(900051068),
(900051063),
(900184598),
(900146682),
(900128865),
(900128515),
(900127855),
(900127839),
(900127813),
(900127836),
(900127770),
(900127719),
(900127735),
(900127742),
(900127686),
(900089079),
(900073752),
(900061328),
(900060237),
(900060313),
(900059251),
(900059281),
(900052735),
(900052666),
(900052586),
(900052410),
(900052409),
(900052405),
(900052404),
(900052403),
(900052311),
(900143620),
(900130756),
(900054329),
(900054835),
(900054983),
(900058310),
(900062099),
(900062115),
(900135370),
(900070979),
(900087302),
(900086232),
(900083350),
(900081185),
(900077923),
(900128485),
(900058984),
(900092300),
(900147082),
(900051296),
(900051301),
(900052123),
(900052272),
(900052286),
(900053522),
(900054221),
(900054239),
(900054535),
(900054767),
(900094926),
(900051934),
(900051935),
(900052021),
(900052085),
(900052229),
(900053582),
(900054090),
(900054094),
(900054145),
(900054151),
(900054416),
(900051913),
(900051917)
UPDATE#TRGT_FLTS
SETTRPT_ID= F.TRPT_ID,
FLEET_ID= F.FLEET_ID
FROM#TRGT_FLTS TF
JOINFLTS F ON TF.TRPT_ID = F.TRPT_ID AND F.DIVISION IS NULL AND F.SUB_DIVISION IS NULL
DECLARE @TRPT_ID INTEGER
DECLARE @FLEET_ID INTEGER
DECLARE MYCURSOR CURSOR FOR SELECT TRPT_ID, FLEET_ID FROM #TRGT_FLTS WHERETRPT_IDIS NOT NULL
OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO @TRPT_ID, @FLEET_ID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #NEW_CLIENT_WDV
SELECTTRPT_ID,
FLEET_ID,
CONTRACT_TYPE_ID,
CLIENT_WDV_ID,
MANUFACTURER_ID,
MODEL_ID,
ADJUSTMENT,
[EXPIRY_DATE],
VARIANT_ID
FROMCLIENT_WDV
WHERETRPT_ID = 900127686
ANDFLEET_ID = 900246227
AND(
MANUFACTURER_ID IN (4,7,13,26)
AND MODEL_ID IN (37,30,15,27)
AND CONTRACT_TYPE_ID IN (3, 61)
)
DELETE FROM CLIENT_WDV
WHERETRPT_ID = @TRPT_ID
ANDFLEET_ID = @FLEET_ID
AND(
MANUFACTURER_ID IN (4,7,13,26)
AND MODEL_ID IN (37,30,15,27)
AND CONTRACT_TYPE_ID IN (3, 61)
)
UPDATE #NEW_CLIENT_WDV
SETTRPT_ID = @TRPT_ID,
FLEET_ID = @FLEET_ID,
CLIENT_WDV_ID = NULL
WHERETRPT_ID = 900127686
ANDFLEET_ID = 900246227
FETCH NEXT FROM MYCURSOR INTO @TRPT_ID, @FLEET_ID
END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR
---------------------------------------------------------------------------------------------
-- ** Get New ID's and INSERT **
DECLARE @NOOFRATES INTEGER
DECLARE @NEXT_RATE_ID INTEGER
DECLARE @RATE_ID INTEGER
SELECT @NOOFRATES = COUNT( * ) FROM #NEW_CLIENT_WDV
EXEC @NEXT_RATE_ID = UP_IDS_GETNEXTID '', @NOOFRATES
DECLARE RATECURSOR CURSOR FOR SELECT CLIENT_WDV_ID FROM #NEW_CLIENT_WDV FOR UPDATE OF CLIENT_WDV_ID
OPEN RATECURSOR
FETCH NEXT FROM RATECURSOR INTO @RATE_ID
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE#NEW_CLIENT_WDV
SETCLIENT_WDV_ID = @NEXT_RATE_ID
WHERE
CURRENT OF RATECURSOR
FETCH NEXT FROM RATECURSOR INTO @RATE_ID
SET @NEXT_RATE_ID = @NEXT_RATE_ID + 1
END
CLOSE RATECURSOR
DEALLOCATE RATECURSOR
INSERT INTO CLIENT_WDV
SELECT * FROM #NEW_CLIENT_WDV
DROP TABLE #TRGT_FLTS
DROP TABLE #NEW_CLIENT_WDV
October 14, 2011 at 6:58 am
can you give a breif description of what the sript is doing?
from a quick look it seems like something that could be acheived using MERGE.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply