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_ID INTEGER,
FLEET_ID INTEGER
)
CREATE TABLE #NEW_CLIENT_WDV
(
TRPT_ID INTEGER NOT NULL,
FLEET_ID INTEGER NOT NULL,
CONTRACT_TYPE_ID INTEGER NOT NULL,
CLIENT_WDV_ID INTEGER,
MANUFACTURER_ID INTEGER NULL,
MODEL_ID INTEGER NULL,
ADJUSTMENT DECIMAL(5, 4) NULL,
[EXPIRY_DATE] DATETIME NULL,
VARIANT_ID INTEGER 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
SET TRPT_ID = F.TRPT_ID,
FLEET_ID = F.FLEET_ID
FROM #TRGT_FLTS TF
JOIN FLTS 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
SELECT TRPT_ID,
FLEET_ID,
CONTRACT_TYPE_ID,
CLIENT_WDV_ID,
MANUFACTURER_ID,
MODEL_ID,
ADJUSTMENT,
[EXPIRY_DATE],
VARIANT_ID
FROM CLIENT_WDV
WHERE TRPT_ID = 900127686
AND FLEET_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
WHERE TRPT_ID = @TRPT_ID
AND FLEET_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
SET TRPT_ID = @TRPT_ID,
FLEET_ID = @FLEET_ID,
CLIENT_WDV_ID = NULL
WHERE TRPT_ID = 900127686
AND FLEET_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
SET CLIENT_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